Bruce, Friday, November 15, 2002, 5:35:21 AM, you wrote: BL> I have a problem about foreign key in using MySQL.com manual. According to BL> the example in MySQL.com manual, one foreign key is ok. However, I have 3 BL> tables (ie. 2 for parent tables and 1 for child table). When I use 2 or more BL> foreign keys, I have the following errors.
BL> Case 1: BL> mysql> create table customerTb ( BL> customerId int not null, BL> primary key (customerId) BL> ) type = innodb; BL> mysql> insert into customerTb values (1), (2), (3), (4), (5); BL> mysql> create table carTb ( BL> carId int not null, BL> primary key (carId) BL> ) type = innodb; BL> mysql> insert into carTb values (1), (2), (3), (4), (5); BL> mysql> create table buyTb ( BL> customerId int not null, BL> carId int not null, BL> primary key (customerId, carId), BL> index i_Tb (customerId, carId), BL> foreign key (customerId) references customerTb (customerId), BL> foreign key (carId) references carTb (carId) BL> on delete cascade BL> ) type = innodb; BL> ERROR 1005 at line 15: Can't create table '.\ibdata\buytb.frm' (errno: 150) Thats correct, because you must have separate indexes (as you did below). BL> Case 2: BL> mysql> create table customerTb ( BL> customerId int not null, BL> primary key (customerId) BL> ) type = innodb; BL> mysql> insert into customerTb values (1), (2), (3), (4), (5); BL> mysql> create table carTb ( BL> carId int not null, BL> primary key (carId) BL> ) type = innodb; BL> mysql> insert into carTb values (1), (2), (3), (4), (5); BL> mysql> create table buyTb ( BL> customerId int not null, BL> carId int not null, BL> primary key (customerId, carId), BL> index i_customerTb (customerId), BL> index i_carTb (carId), BL> foreign key (customerId) references customerTb (customerId), BL> foreign key (carId) references carTb (carId) BL> on delete cascade BL> ) type = innodb; BL> mysql> insert into buyTb values (1, 1), (1, 2), (1, 3), (2, 3), (4, 3), (4, BL> 4), (4, 5), (5, 5), (5, 1); BL> mysql> delete from customerTb where customerId = 1; BL> ERROR 1217: Cannot delete a parent row: a foreign key constraint fails Thats correct too. Because you specify ON DELETE CASCADE only for the second foreign key constraint. BL> I have thought it for more than three nights, but I still have no solution BL> for this. Can the manual state whether Innodb can support more then one BL> foreign key or not? It makes me so confused. InnoDB can have more than one foreign key. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com --------------------------------------------------------------------- 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
