Hello,
In Case 1, you need to change:
"index i_Tb (customerId, carId)" => "index i_Tb (carId,
customerId)" or add "index i_carId (carId)"
In Case 2, you need :
"foreign key (customerId) references customerTb (customerId)" =>
"foreign key (customerId) references customerTb (customerId) on delete
cascade".
José Ceferino Ortega
-----Mensaje original-----
De: Bruce Lee [mailto:itnobita@;hotmail.com]
Enviado el: viernes, 15 de noviembre de 2002 4:35
Para: [EMAIL PROTECTED]
Asunto: Foreign Keys and InnoDb in MySQL
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),
mysql> (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
---------------------------------------------------------------------
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
