Foreign Keys and InnoDb in MySQL

[itnobita]

Hello,

I have a problem about foreign key in using MySQL. I have 3 tables (ie. 2 for parent tables and 1 for child table). According to the exmaple in MySQL.com manual, one foreign key is ok. However, when I use 2 or more foreign keys, I have the following errors.

Case 1:

mysql> create table customerTb (
customerId int not null,
primary key (customerId)
) type = innodb;
mysql> insert into customerTb values (1), (2), (3), (4), (5);

mysql> create table carTb (
carId int not null,
primary key (carId)
) type = innodb;
mysql> insert into carTb values (1), (2), (3), (4), (5);

mysql> create table buyTb (
customerId int not null,
carId int not null,
primary key (customerId, carId),
index i_Tb (customerId, carId),
foreign key (customerId) references customerTb (customerId),
foreign key (carId) references carTb (carId)
on delete cascade
) type = innodb;
ERROR 1005 at line 15: Can't create table '.\ibdata\buytb.frm' (errno: 150)

Case 2:

mysql> create table customerTb (
customerId int not null,
primary key (customerId)
) type = innodb;
mysql> insert into customerTb values (1), (2), (3), (4), (5);

mysql> create table carTb (
carId int not null,
primary key (carId)
) type = innodb;
mysql> insert into carTb values (1), (2), (3), (4), (5);

mysql> create table buyTb (
customerId int not null,
carId int not null,
primary key (customerId, carId),
index i_customerTb (customerId),
index i_carTb (carId),
foreign key (customerId) references customerTb (customerId),
foreign key (carId) references carTb (carId)
on delete cascade
) type = innodb;
mysql> insert into buyTb values (1, 1), (1, 2), (1, 3), (2, 3), (4, 3), (4, 4), (4, 5), (5, 5), (5, 1);

mysql> delete from customerTb where customerId = 1;
ERROR 1217: Cannot delete a parent row: a foreign key constraint fails

I have thought it for more than three nights, but I still have no solution for this. Can anyone help me?

My Email is: [e-mail]

Thanks a lot!

Nobita

[Sxooter]

Does MySQL use the references SQL syntax?

i.e.

create table child (
id int,
p_id int references parent (id)
)

[barand]

Looks like your second error is just your foreign key constraint doing its job. You can't delete customer 1 because there are child records still in the table.

[barand]

Nobita - check out your MySql manual, mine says that InnoDB does not yet support ON DELETE CASCADE, (as you have with your carID reference. ) but it may have been introduced since my version, my manual is dated 2001.

Sxooter - tables of type InnoDB support Foreign Key references.

[chriskl]

MySQL's "foreign keys" do not support any cascade options. I suggest using PostgreSQL instead if you can.

Chris