Creating a foreign key in MySQL produces error:
I'm trying to create a foreign key on a table in MySQL and I'm getting a strange error that there seems to be little info about in any of my searches.
I'm creating the key with this (emitted from mysql workbench 5.2):
ALTER TABLE `db`.`appointment`
ADD CONSTRAINT `FK_appointment_CancellationID`
FOREIGN KEY (`CancellationID` ) REFERENCES `db`.`appointment_cancellation` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION
, ADD INDEX `FK_appointment_CancellationID` (`CancellationID` ASC) ;
at which point I get the error:
ERROR 1452: Cannot add or update a child row: a foreign key constraint f开发者_StackOverflow社区ails (
alarmtekcore
., CONSTRAINTFK_lead_appointment_CancellationID
FOREIGN KEY (CancellationID
) REFERENCESlead_appointment_cancellation
(`)
I've checked here
but there's no data in the table.
You can't apply a foreign key constraint on a column with pre-existing data that doesn't already exist in the parent table.
If you run the following to populate the appointment_cancellation table, you should be able to apply the foreign key afterwards:
INSERT INTO appointment_cancellation
SELECT DISTINCT a.CancellationID
FROM appointment
The two fields - appointment
.CancellationID
and appointment_cancellation
.ID
- need to be exactly the same type. If one is INT and the other is INT UNSIGNED, you'll get this error.
精彩评论