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.

When you say there's no data - do you mean the `appointment_cancellation` table? — OMG Ponies, Jun 08 '10 at 22:10

score 4 · Accepted Answer · answered Jun 08 '10 at 22:18

4

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

answered Jun 08 '10 at 22:18

OMG Ponies

325,700
82
523
502

Maybe I don't understand correctly; the appointment_cancellation table is a new addition to the DB (and has no records). In the appointment table, all values for CancellationID are null (default). – Steven Evers Jun 08 '10 at 22:25
@SnOrfus: A record has to exist in `appointment_cancellation`, even if the value is null - that's what a foreign key constraint is. It ensures that the only values that can exist in the child, are already present in the parent. – OMG Ponies Jun 08 '10 at 22:29

score 0 · Answer 2 · answered Jun 08 '10 at 22:24

0

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.

answered Jun 08 '10 at 22:24

pzr

1,226
9
6

Thank you for your input, that was one of the first things I checked (that exact scenario has happened to me in the past). – Steven Evers Jun 09 '10 at 02:10

Creating a foreign key in MySQL produces error:

2 Answers2