Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails

I'm having a bit of a strange problem. I'm trying to add a foreign key to one table that 

references another, but it is failing for some reason. With my limited knowledge of MySQL, 

the only thing that could possibly be suspect is that there is a foreign key on a different 

table referencing the one I am trying to reference.

Here is a picture of my table relationships, generated via phpMyAdmin: 

I've done a SHOW CREATE TABLE query on both tables, sourcecodes_tags

 is the table with the foreign key, sourcecodes is the referenced table.

CREATE TABLE `sourcecodes` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) unsigned NOT NULL, `language_id` int(11) unsigned NOT NULL, `category_id` int(11) unsigned NOT NULL, `title` varchar(40) CHARACTER SET utf8 NOT NULL, `description` text CHARACTER SET utf8 NOT NULL, `views` int(11) unsigned NOT NULL, `downloads` int(11) unsigned NOT NULL, `time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `language_id` (`language_id`), KEY `category_id` (`category_id`), CONSTRAINT `sourcecodes_ibfk_3` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) 

ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `sourcecodes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) 

ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `sourcecodes_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) 

ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1CREATE TABLE `sourcecodes_tags` ( `sourcecode_id` int(11) unsigned NOT NULL, `tag_id` int(11) unsigned NOT NULL, KEY `sourcecode_id` (`sourcecode_id`), KEY `tag_id` (`tag_id`), CONSTRAINT `sourcecodes_tags_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) 

ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1

It would be great if anyone could tell me what is going on here, I've had no formal 

training or anything with MySQL :)

Edit: This is the code that generates the error:

ALTER TABLE sourcecodes_tags ADD FOREIGN KEY (sourcecode_id) 

REFERENCES sourcecodes (id) ON DELETE CASCADE ON UPDATE CASCADE

 Answers

Quite likely your sourcecodes_tags table contains sourcecode_id 

values that no longer exists in your sourcecodes table. You have to get rid of those first.

Here's a query that can find those IDs:

SELECT DISTINCT sourcecode_id FROM 
   sourcecodes_tags tags LEFT JOIN sourcecodes sc ON tags.sourcecode_id=sc.id 
WHERE sc.id IS NULL;

---

Use NOT IN to find where constraints are constraining:

SELECT column FROM table WHERE column NOT IN 
(SELECT intended_foreign_key FROM another_table)

so, more specifically:

SELECT sourcecode_id FROM sourcecodes_tags WHERE sourcecode_id NOT IN 
(SELECT id FROM sourcecodes)

EDIT: IN and NOT IN operators are known to be much faster than the JOIN 

operators, as well as much easier to construct, and repeat.

---

This also happens when setting a foreign key to parent.id to child.column if the 

child.column has a value of 0 already and no parent.id value is 0

You would need to ensure that each child.column is NULL or has value that exists in parent.id

And now that I read the statement nos wrote, that's what he is validating.

---

I had the same problem today. I tested for four things, some of them already mentioned 

here:

• Are there any values in your child column that don't exist in the parent column

 (besides NULL, if the child column is nullable)

• Do child and parent columns have the same datatype?

• Is there an index on the parent column you are referencing? MySQL seems to require this 

for performance reasons (http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html)

• And this one solved it for me: Do both tables have identical collation?

I had one table in utf-8 and the other in iso-something. That didnt't work. After 

changing the iso-table to utf-8 collation the constraints could be added without problems. 

In my case, phpMyAdmin didn't even show the child table in iso-encoding in the

 dropdown for creating the foreign key constraint.

---

I'd the same problem,I checked rows of my tables and found there were some 

incompatibility with value of fields that I wanted to define as foreign key. 

I corrected those value, tried again and problem was solved.

---

try this

SET foreign_key_checks = 0; ALTER TABLE sourcecodes_tags 

ADD FOREIGN KEY (sourcecode_id) REFERENCES sourcecodes (id) 

ON DELETE CASCADE ON UPDATE CASCADE SET foreign_key_checks = 1;

---

Empty both your tables' data and run the command. It will work.

---

I have a solution, you just need to answer one question:

Is your table already storing data? Especially the table included foreign key.

If the answer is yes, then the only thing you do is delete all the record and then you 

are free to add in any foreign key in your table.

Delete instruction: From child(which include foreign key table) to parent table.

The reason you cannot add in foreign key after data entries is due to the table 

inconsistency, what are you going to deal with new foreign key on former data filled table?

If no, then follow others instructions.

---

Make sure the value is in the other table otherwise you will get this error, 

in the assigned corresponding column.

So if it is assigned column is assigned to a row id of another table , make 

sure there is a row that is in the table otherwise this error will appear.

---

I had the same problem and found solution, placing NULL instead of NOT NULL 

on foreign key column. Here is a query:

ALTER TABLE `db`.`table1`
ADD COLUMN `col_table2_fk` INT UNSIGNED NULL,
ADD INDEX `col_table2_fk_idx` (`col_table2_fk` ASC),
ADD CONSTRAINT `col_table2_fk1`
FOREIGN KEY (`col_table2_fk`)
REFERENCES `db`.`table2` (`table2_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

MySQL has executed this query!

I have created tables in MySQL Workbench as shown below :

ORDRE table:

CREATE TABLE Ordre (
  OrdreID   INT NOT NULL,
  OrdreDato DATE DEFAULT NULL,
  KundeID   INT  DEFAULT NULL,
  CONSTRAINT Ordre_pk PRIMARY KEY (OrdreID),
  CONSTRAINT Ordre_fk FOREIGN KEY (KundeID) REFERENCES Kunde (KundeID)
)
  ENGINE = InnoDB;

PRODUKT table:

CREATE TABLE Produkt (
  ProduktID          INT NOT NULL,
  ProduktBeskrivelse VARCHAR(100) DEFAULT NULL,
  ProduktFarge       VARCHAR(20)  DEFAULT NULL,
  Enhetpris          INT          DEFAULT NULL,
  CONSTRAINT Produkt_pk PRIMARY KEY (ProduktID)
)
  ENGINE = InnoDB;

and ORDRELINJE table:

CREATE TABLE Ordrelinje (
  Ordre         INT NOT NULL,
  Produkt       INT NOT NULL,
  AntallBestilt INT DEFAULT NULL,
  CONSTRAINT Ordrelinje_pk PRIMARY KEY (Ordre, Produkt),
  CONSTRAINT Ordrelinje_fk FOREIGN KEY (Ordre) REFERENCES Ordre (OrdreID),
  CONSTRAINT Ordrelinje_fk1 FOREIGN KEY (Produkt) REFERENCES Produkt (ProduktID)
)
  ENGINE = InnoDB;

so when I try to insert values into ORDRELINJE table i get:

Error Code: 1452. Cannot add or update a child row: a foreign key 

constraint fails (srdjank.Ordrelinje, CONSTRAINT Ordrelinje_fk 

FOREIGN KEY (Ordre) REFERENCES Ordre (OrdreID))

Taken from Using FOREIGN KEY Constraints

Foreign key relationships involve a parent table that holds the central data values, 

and a child table with identical values pointing back to its parent.

 The FOREIGN KEY clause is specified in the child table.

It will reject any INSERT or UPDATE operation that attempts to create 

a foreign key value in a child table if there is no a matching candidate key 

value in the parent table.

So your error Error Code: 1452. Cannot add or update a child row: a 

foreign key constraint fails essentially means that, you are trying to add a 

row to your Ordrelinje table for which no matching row (OrderID) is present in

 Ordre table.

You must first insert the row to your Ordre table.

---

You must delete data in the child table which does not have any corresponding 

foreign key value to the parent table primary key .Or delete all data from the child 

table then insert new data having the same foreign key value as the primary key in 

the parent table . That should work

---

Your ORDRELINJE table is linked with ORDER table using a foreign key constraint 

constraint Ordrelinje_fk foreign key(Ordre) references Ordre(OrdreID)

 according to which Ordre int NOT NULL, column of table ORDRELINJE must match 

any Ordre int NOT NULL, column of ORDER table.

Now what is happening here is, when you are inserting new row into ORDRELINJE table, 

according to fk constraint Ordrelinje_fk it is checking ORDER table if the OrdreID is

 present or not and as it is not matching with any OrderId, Compiler is complaining for

 foreign key violation. This is the reason you are getting this error.

Foreign key is the primary key of the other table which you use in any table to link

 between both. This key is bound by the foreign key constraint which you specify while 

creating the table. Any operation on the data must not violate this constraint. Violation 

of this constraint may result in errors like this.

Hope I made it clear.