SQL Error

[Gerry Mueller]

Hi Guys and Ladies(of course!)

I created the table lieferanten successfully! Like this

    
mysql> CREATE TABLE Lieferanten(
    ->   P_Id int(8) NOT NULL AUTO_INCREMENT,
    ->   login varchar(15) 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 (P_Id, login)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>

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]

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)"

[Gerry Mueller]

Thank for your answer. I am working under windows.
But I solve the problem proceeding otherweise.

Thanks

[Peter Johnson]

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]

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!