mysql foreign key error #1452

Question

ALTER TABLE  `groups` ADD FOREIGN KEY (  `company_id` ) REFERENCES  `summaries`.`companies` (

`id`
) ON DELETE CASCADE ;

MySQL said: 

#1452 - Cannot add or update a child row: a foreign key constraint fails (`summaries/#sql-164a_33c`, CONSTRAINT `#sql-164a_33c_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE) 

companies.id is primary auto increment int(11)

company_id is index int(11)

I don't understand the error message. Can anyone shed some light on this?

Answer 1

That means you have at least one row in the child table that references a non-existent row in the parent table.

If you are absolutely sure that you are okay with having a data integrity issue like that, you can add the foreign key by disabling foreign key checks before you run the ALTER TABLE command:

SET FOREIGN_KEY_CHECKS = 0;

Answer 2

I just had this problem, although in a somewhat more specific scenario.

In my case, I had added, to an existing table, a column that I needed to be both nullable and act as a foreign key (i.e., for non-null entries to be bound by a foreign key constraint).

The referenced column looked like this:

+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(10)     | NO   | PRI | NULL    | auto_increment |
+-------------+-------------+------+-----+---------+----------------+

and the referencing one like this:

+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| bed_id      | int(10)     | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

Turned out that I had forgotten to specify DEFAULT NULL when adding the referencing column to the existing table and so it was automatically filled with zeros, which failed the foreign key constraint.

I changed them to NULL:

update devices set bed_id = NULL where bed_id = 0;

and then successfully added the foreign key constraint. Hope this helps someone