aspose file tools*
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 The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript 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: 5823
    
    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: 5823
    
    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