ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

Question

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))

I've seen the other posts on this topic, but no luck. Am I overseeing something or any idea what to do?

possible duplicate of Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails — Sagar Naliyapara
– Sagar Naliyapara, Commented Jul 16, 2015 at 5:07
You need to empty your table before you add this foreign key — Nakvi
– Nakvi, Commented Mar 13, 2023 at 7:27

Rahul · Accepted Answer · 2020-05-23 21:00:04Z

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.

Or can we drop the foreign key then add foreign key after data insertion?
@VamsiPavanMahesh, NO, cause even if you do that; your foriegn key creation will fail with same kind of error since there will be key data mismatch.
Simply speaking if children have parent ids defined, these parents have to exist. And I thought my syntax was wrong... It helped me a lot. Thanks!
@Rahul but in my case data is existed in parent table but at a sudden 3 years old database (child table) stop saving the data i.sstatic.net/dMl6m.png

Mr-Faizan · Accepted Answer · 2019-03-27 08:18:33Z

117

The Problem is with FOREIGN KEY Constraint. By Default (SET FOREIGN_KEY_CHECKS = 1). FOREIGN_KEY_CHECKS option specifies whether or not to check foreign key constraints for InnoDB tables. MySQL - SET FOREIGN_KEY_CHECKS

We can set foreign key check as disable before running Query. Disable Foreign key.

Execute one of these lines before running your query, then you can run your query successfully. :)

1) For Session (recommended)

SET FOREIGN_KEY_CHECKS=0;

2) Globally

SET GLOBAL FOREIGN_KEY_CHECKS=0;

answered Mar 27, 2019 at 8:18

Mr-Faizan

1,2871 gold badge9 silver badges10 bronze badges

4 Comments

Klaus M. Over a year ago

Yeah but the important thing is, after setting the foreign key constraint to 0, don't forget to set it back

dehart Over a year ago

This is basically adding a foreign key constrain to guarantee data integrity (making sure the "Ordre" that is referenced exists) and then disable checking for it while inserting the reference.

Félix Adriyel Gagnon-Grenier Over a year ago

There's exactly one roughly reasonable use of setting constraints off, and that is sourcing a whole data dump from a file. If one creates the foreign keys before dumping the data, it can happen that at the moment a row is inserted its foreign relation doesn't exist yet. I say "roughly" because in that situation, one should almost always choose to create the indices after data was inserted, but reality can get in the way of that.

Félix Adriyel Gagnon-Grenier Over a year ago

However, any turning off of db constraints that is used to get around data corruption, where child rows point to non-existing parents, is objectively the worse possible way of dealing with the problem. If one wants data to not be normalized, then don't use foreign keys. If foreign keys are defined, then the data must follow it, otherwise delete the foreign keys. When encountering the 1452 error in a context where foreign keys are legitimate, one should rather remove orphaned rows, and then set the foreign keys, see Madhur's answer.

Madhur Bhaiya · Accepted Answer · 2020-07-22 03:13:01Z

This error generally occurs because we have some values in the referencing field of the child table, which do not exist in the referenced/candidate field of the parent table.

Sometimes, we may get this error when we are applying Foreign Key constraints to existing table(s), having data in them already. Some of the other answers are suggesting to delete the data completely from child table, and then apply the constraint. However, this is not an option when we already have working/production data in the child table. In most scenarios, we will need to update the data in the child table (instead of deleting them).

Now, we can utilize Left Join to find all those rows in the child table, which does not have matching values in the parent table. Following query would be helpful to fetch those non-matching rows:

SELECT child_table.* FROM child_table LEFT JOIN parent_table ON parent_table.referenced_column = child_table.referencing_column WHERE parent_table.referenced_column IS NULL

Now, you can generally do one (or more) of the following steps to fix the data.

Based on your "business logic", you will need to update/match these unmatching value(s), with the existing values in the parent table. You may sometimes need to set them null as well.
Delete these rows having unmatching values.
Add new rows in your parent table, corresponding to the unmatching values in the child table.

Once the data is fixed, we can apply the Foreign key constraint using ALTER TABLE syntax.

Community · Accepted Answer · 2023-01-23 08:25:13Z

51

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 .

edited Jan 23, 2023 at 8:25

CommunityBot

11 silver badge

answered Mar 6, 2018 at 7:24

Humphrey

2,8273 gold badges30 silver badges39 bronze badges

1 Comment

Deepak Panwar Over a year ago

This is correct, before inserting data into the pivot table (that having CASCADE constraints), you must insert data into parent tables. e.g 1st table - permissions; 2nd table - roles; 3rd table - permission_role; So 1st Insert into permissions table, 2nd insert into roles table and at last insert into permission_role table.

Aleksi Baklanov · Accepted Answer · 2019-10-25 16:26:33Z

You are getting this constraint check because Ordre table does not have reference OrdreID provided in insert command.

To insert value in Ordrelinje, you first have to enter value in Ordre table and use same OrdreID in Orderlinje table.

Or you can remove not null constraint and insert a NULL value in it.

Sagar Wankhede · Accepted Answer · 2020-01-16 11:40:20Z

This helped me out after reading @Mr-Faizan's and other answers.

Untick the 'Enable foreign key checks'

in phpMyAdmin and hit the query. I don't know about WorkBench but the other answers might help you out.

Dawood F.M Kaundama · Accepted Answer · 2020-09-23 12:11:45Z

I had the same problem. I was creating relationships on existing tables but had different column values, which were supposed/assumed to be related. For example, I had a table USERS that had a column USERID with rows 1,2,3,4,5. Then I had another child table ORDERS with a column USERID with rows 1,2,3,4,5,6,7. Then I run MySQl command ALTER TABLE ORDERS ADD CONSTRAINT ORDER_TO_USER_CONS FOREIGN KEY (ORDERUSERID) REFERENCES USERS(USERID) ON DELETE SET NULL ON UPDATE CASCADE;

It was rejected with the message:

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (DBNAME1.#sql-4c73_c0, CONSTRAINT ORDER_TO_USER_CONS FOREIGN KEY (ORDERUSERID) REFERENCES USERS (USERID) ON DELETE SET NULL ON UPDATE CASCADE)

I exported data from the ORDERS table, then deleted all data from it, re-run the command again, it worked this time, then re-inserted the data with the corresponding USERIDs from the USERS table.

Spinstaz · Accepted Answer · 2021-11-12 22:20:02Z

I found that changing the foreign key back from not null to null BEFORE I tried to do what I knew was the correct code, got it working. Helped that I was using Mysql workbench. I had to also set SET FOREIGN_KEY_CHECKS=0; and then back to =1; after finished.

Edoardo Vignati · Accepted Answer · 2020-09-09 08:13:32Z

In my case the tables were perfectly consistent.

Anyway I was getting this error because I created (by accident) more than one FK constraint on the same field.

I run the following query to show all the keys:

SELECT * FROM information_schema.table_constraints WHERE constraint_schema = 'my_db_name'

and I deleted the wrong ones with the following query:

ALTER TABLE my_table DROP FOREIGN KEY wrong_fk_constraint;

You can check it also running this query:

SHOW CREATE TABLE my_table;

Jeff Ogah · Accepted Answer · 2020-08-18 11:44:34Z

First allow NULL on the parent table and set the default values to NULL. Next create the foreign key relationship. Afterwards, you can update the values to match accordingly

Dharman · Accepted Answer · 2024-04-22 12:11:16Z

This can be fixed by inserting the respective records in the Parent table first and then we can insert records in the Child table's respective column. Also, check the data type and size of the column. It should be the same as the parent table column, even the engine and collation should also be the same.

Ahmad · Accepted Answer · 2016-10-29 11:24:47Z

While inserting the foreign key attribute values, first verify the attributes type, as well as primary key attribute value in the parent relation, if the values in parent relation matches, then you can easily insert/update child attribute values.

Dharman · Accepted Answer · 2024-04-22 12:00:29Z

Don't botch the constraints and your db structure. That is easy, check both of the columns you would like to connect again. They must have the same datatype and length. If they are not just modify the incorrect one by alter the table. If they are the same, check the data you already have within them. The logic of this relation is that you must be able to find any value from the foreign key column within one table inside the primary column from another table. As an example, let's say you have:

------------------- ------------------- | Table A | | Table B | ------------------- ------------------- | PK | ref_id | 1 | | PK | b_id | 1 | ------------------- ------------------- | FK | b_id | 0 | -------------------

In this example, b_id column of table A could not connect to b_id column of table B because there is no record for 0 To solve that add / remove / edit the content of the tables and not the constraints.

Dharman · Accepted Answer · 2024-04-22 12:15:22Z

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.

Jeremy · Accepted Answer · 2022-03-15 19:58:12Z

Probably better answered above, but when working in mysql workbench you don't need to commit the transaction immediatly, you can commit the parent and child element at the same time. So setup the parent with sql or in the gui and add the child in the gui / sql and commit concurrently.

If working in code and getting this issue you can create a factory to create the parent and then create the child / join.

Theoretically you would need an Order to have an OrderId thus create an Order. The create an OrderId and that OrderId may have a number of associated products which you can then add to the OrderId or do with as you wish.

Rajat Upadhyay · Accepted Answer · 2023-04-10 16:29:14Z

I faced the same issue while using Sequelize with Nodejs/ExpressJS server and trying to insert data into a News table which had a foreign key to a Users table, basically, the user who created the news.

In my case, the problem was I had created a foreign key reference from the News table to Users table using .belongsTo() method provided by the sequelize API. Though I had not referenced a foreign key from Users to News using .hasMany(). Once I did this, then re-populated my table with data using Model.create({}), it worked!

hoogw · Accepted Answer · 2025-06-06 19:59:36Z

by default, phpmyadmin will check foreign key, you must un-check, otherwise, you will get this error

Nicole Cook · Accepted Answer · 2021-11-29 00:21:46Z

Just something else to look for. If you had to delete records from one of your tables and are expecting the values to start at 1, you could get this error. The solution was to run a SHOW * FROM tablename on all the Parent tables. When I did I noticed in one of the tables where I had had a problem earlier and had to delete some records that the primary key values were not what I was expecting them to be from a previous SELECT *.

Collectives™ on Stack Overflow

ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

18 Answers 18

7 Comments

4 Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

18 Answers 18

7 Comments

4 Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Linked

Related