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
