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 primary auto increment int(11)

company_id - index int(11)

I do not understand the error message. Can anyone shed some light on this?

Answer 1

This means that you have at least one row in the child table that references a nonexistent row in the parent table.

If you are absolutely sure that you have a data integrity problem, you can add a foreign key by disabling foreign key checks before running the command ALTER TABLE:

SET FOREIGN_KEY_CHECKS = 0;

Answer 2

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

, (.. , ).

:

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

:

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

, DEFAULT NULL , .

NULL:

update devices set bed_id = NULL where bed_id = 0;

. , -.

Answer 3

, .

MySQL first tried to put the requested restrictions in the temp table. A group table can have one or more rows (hence also the temp table), whose company_id is no longer in the summaries.companies table.

In verfiy: try running a LEFT JOIN between groups and summaries.companies. WHERE company.id NULL. If you return any rows from this LEFT JOIN, you find bad rows in the group table.

Give it a try !!!

Answer 4

Checks that the "company" table is not empty if it is empty and you do not have data at the moment.

 set SET FOREIGN_KEY_CHECKS = 0; 

as Ike said before.