CODEWITHSUNDEEP
mysqlforeign-keys
Bohdan V.
Bohdan V.

Reputation: 141

How to fix error in MySQL : 1452 - Cannot add or update a child row: a foreign key constraint fails

I run sql query in Navicat, so got error;

Query:

ALTER TABLE `customer_eav_attribute`
  ADD CONSTRAINT `CUSTOMER_EAV_ATTRIBUTE_ATTRIBUTE_ID_EAV_ATTRIBUTE_ATTRIBUTE_ID` 
  FOREIGN KEY (`attribute_id`) 
  REFERENCES `eav_attribute` (`attribute_id`) 
  ON DELETE CASCADE;

Error:

1452 - Cannot add or update a child row: a foreign key constraint
fails (`caterin1_test`.`#sql-dd4_13`, CONSTRAINT
`CUSTOMER_EAV_ATTRIBUTE_ATTRIBUTE_ID_EAV_ATTRIBUTE_ATTRIBUTE_ID`
FOREIGN KEY (`attribute_id`) 
REFERENCES `eav_attribute` (`attribute_id`) 
ON DELETE CA)

How can I fix it?

Upvotes: 4

Views: 6192

Answers (4)

JennD
JennD

Reputation: 1

When I encountered this error, it was because I had data in the child table that did not map to a primary key in the parent. I ran an outer join query to find the incorrect records, then removed them. I was able to create the one-to-many relationship after all data in the child table could be related to the parent.

Upvotes: -1

Super W Star
Super W Star

Reputation: 21

The parent table must exist before you define a foreign key to reference it. you must define the tables in the right order: Parent table first, then the child table. If both tables references each other, you must create one table without FK constraints, then create the second table, then add the FK constraint to the first table with ALTER TABLE.

Upvotes: 2

Stevan stajo
Stevan stajo

Reputation: 31

This is usually happening when you try to source file into existing database. Drop two tables(customer_eav_attribute, eav_attribute). Please create table again.

CREATE TABLE t1
(id INTEGER);

CREATE TABLE t2
(t1_id INTEGER,
 CONSTRAINT FOREIGN KEY (t1_id) REFERENCES t1 (id));

And then set like this.

ALTER TABLE `customer_eav_attribute`
  ADD CONSTRAINT `CUSTOMER_EAV_ATTRIBUTE_ATTRIBUTE_ID_EAV_ATTRIBUTE_ATTRIBUTE_ID` 
  FOREIGN KEY (`attribute_id`) 
  REFERENCES `eav_attribute` (`attribute_id`) 
  ON DELETE CASCADE;

Upvotes: 3

Shiba Y.
Shiba Y.

Reputation: 89

I think there is no linked id in eav_attribute table and customer_eav_attribute table. You must check eav_attribute table and customer_eav_attribute table (best way: plz delete eav_attribute and customer_eav_attribute and then insert data again). You can find solution.

Upvotes: 2

Related Questions