Foreign Keys and InnoDb in MySQL

[Bruce Lee]

Hello,

I have a problem about foreign key in using MySQL.com manual. According to the example in MySQL.com manual, one foreign key is ok. However, I have 3 tables (ie. 2 for parent tables and 1 for child table). 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 the manual state whether Innodb can support more then one foreign key or not? It makes me so confused.

Thanks a lot!

Nobita

_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail


---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php