This week's book giveaway is in the Java 8 forum.
We're giving away four copies of Java 8 in Action and have Raoul-Gabriel Urma, Mario Fusco, and Alan Mycroft on-line!
See this thread for details.
The moose likes JDBC and the fly likes SQL Error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Error" Watch "SQL Error" New topic
Author

SQL Error

Gerry Mueller
Greenhorn

Joined: Dec 30, 2009
Posts: 17
Hi Guys and Ladies(of course!)

I created the table lieferanten successfully! Like this



Now when try to create the table "lieferanten_roles" I got this error:


mysql> CREATE TABLE Lieferanten_roles(
-> P_Id int(8) NOT NULL AUTO_INCREMENT,
-> login varchar(15) NOT NULL,
-> roles varchar(10) NOT NULL,
-> PRIMARY KEY (P_Id),
-> FOREIGN KEY (login) references lieferanten(login)
-> on update cascade on delete cascade);
ERROR 1005 (HY000): Can't create table 'webapp.lieferanten_roles' (errno: 150)
mysql>


I know there is a problem with the foreign key occured. But I can not check it

Do anybody have an idea?
Peter Johnson
author
Bartender

Joined: May 14, 2008
Posts: 5779
    
    7

Perhaps a capitalization error? Some installations of MySQL, especially on Linux, are case sensitive.

The table name is: Lieferanten
The foreign key: "references lieferanten(login)"


JBoss In Action
Gerry Mueller
Greenhorn

Joined: Dec 30, 2009
Posts: 17
Thank for your answer. I am working under windows.
But I solve the problem proceeding otherweise.

Thanks
Peter Johnson
author
Bartender

Joined: May 14, 2008
Posts: 5779
    
    7

Gerry, please describe what you did to solve the problem as a courtesy for future Ranchers who have the same problem you had an come across your post.
Gerry Mueller
Greenhorn

Joined: Dec 30, 2009
Posts: 17
HI Johnson,

excuse you're correct!

In the fact, the problem concert the foreign key. When setting a foreign key the primary key in the index-table (here lieferanten and key login)
must be the first column.

I got it when enter the following command in the MySQL Command Line Client: "show innodb status" and read the paragrap
last innodb error or something like that.

then my tables now look like follow:;


CREATE TABLE Lieferanten(
login varchar(15) NOT NULL,
P_Id int(8) NOT NULL,
firm varchar(50) NOT NULL,
contact_person varchar(50) NOT NULL,
telefon varchar(20) NOT NULL,
pwd varchar(15) NOT NULL,
email varchar(20) NOT NULL,
PRIMARY KEY (login)
);

ALTER TABLE Lieferanten AUTO_INCREMENT=1000;

----------

CREATE TABLE Lieferanten_roles(
P_Id int(8) NOT NULL AUTO_INCREMENT,
login varchar(15) NOT NULL,
roles varchar(10) NOT NULL,
PRIMARY KEY (P_Id),
FOREIGN KEY (login) references lieferanten(login)
on update cascade on delete cascade);



And it work!
Bye bye!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Error
 
Similar Threads
ManyToOne relation with composite keys
how to related two tables
Hibernate tool code generation org.hibernate.MappingException: Foreign key
How to restrict user against marking same event in multiple times
Hibernate is driving me crazy !