error adding a foreign key constraint

问题:

I have the following query:

ALTER TABLE ROUTE ADD FOREIGN KEY (RID) REFERENCES RESERVATION(RID) ON DELETE CASCADE

but it generates me an error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`SmarTrek`.`#sql-91e_d09`, CONSTRAINT `FK_RID` FOREIGN KEY (`RID`) REFERENCES `RESERVATION` (`RID`) ON DELETE CASCADE)

In designer mode, here's what it looks like:

回答1:

That would meant that you already have data in the ROUTE table that does not satisfy the foreign key constraint.

To find the offending records, so you can update them to some other value (that exists), you can use

select *
from route
where rid not in (select rid from reservation)

回答2:

THERE may b 2 reasons

• there may b some row in ROUTE TABLE which have RID that does not exist in RESERVATION(RID)
• or check the DATATYPE OF ROUTE (RID) & RESERVATION(RID) both should be same ( unsigned/signed)