Creating a foreign key in MySQL produces error:

Question

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 fails (alarmtekcore., CONSTRAINT FK_lead_appointment_CancellationID FOREIGN KEY (CancellationID) REFERENCES lead_appointment_cancellation (`)

I've checked here

but there's no data in the table.

Answer 1

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

Answer 2

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.