CODEWITHSUNDEEP
mysql
Ben
Ben

Reputation: 62356

mysql foreign key error #1452

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?

Upvotes: 7

Views: 35519

Answers (4)

adridev
adridev

Reputation: 1170

Checks that "companies" table is not empty,if is empty and you have no data at moment.

 set SET FOREIGN_KEY_CHECKS = 0;

like Ike said you before.

Upvotes: 0

mcmlxxxvi
mcmlxxxvi

Reputation: 1409

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

Upvotes: 5

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

It looks like it tried to copy the groups table to a temp table in the summaries database.

MySQL tried to put the requested constraints on the temp table first. There may possibly be one or more rows in the groups table (hence the temp table also) whose company_id is no longer present in the summaries.companies table.

To verfiy : Try running a LEFT JOIN between groups and summaries.companies WHERE companies.id is NULL. If you get back any rows from that LEFT JOIN, you found the bad rows in the groups table.

Give it a Try !!!

Upvotes: 1

Ike Walker
Ike Walker

Reputation: 65527

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;

Upvotes: 31

Related Questions