• Register
0 votes
1.6k views

Problem :

I have created the tables in MySQL Workbench. But when I try to insert the values into the ORDRELINJE table i get below error:

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 have seen many other posts on this topic but unable to fix my issue. How can I fix this issue?

7 5 2
3,870 points

Please log in or register to answer this question.

2 Answers

0 votes

Solution :

The foreign key relationships involve the parent table that holds a central data values, and the child table with a identical values pointing back to the parent. A FOREIGN KEY clause is specified in a child table.

It will reject any INSERT or UPDATE operation that attempts to create the foreign key value in the child table if there is no the matching candidate key value in a parent table.

So you are facing error Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails essentially it means that, you are trying to add the row to the Ordrelinje table for which there is no matching row (OrderID) present in Ordre table.

So you must first insert a row to your Ordre table.

This will fix your issue.

9 7 4
38,600 points
0 votes

Solution:

Foreign key relationships entangled a parent table that contains the central data values, and a child table with identical values indicating back to its parent. The FOREIGN KEY clause is secluded in the child table.

It will refuse any INSERT or UPDATE operation that tries to make a foreign key value in a child table in case there is no a matching candidate key value in the parent table.

Hence your error Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails originally refers that, you are attempting to include a row to your Ordrelinje table for which no matching row (OrderID) is present in Ordre table.

You should first insert the row to your Ordre table.

You are obtaining this constraint check since Ordre table does not have reference OrdreID given in insert command.

For  inserting the value in Ordrelinje, you first have to enter value in Ordre table and use same OrdreID in Orderlinje table.

Another way,  you can remove not null constraint and insert a NULL value in it.

You should delete data in the child table which does not have any similar foreign key value to the parent table primary key .Or delete all data from the child table then insert new data having the similar foreign key value as the primary key in the parent table .

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

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

Execute one of these lines prior running your query, then you can run your query effectively. 

1) For Session (recommended)

SET FOREIGN_KEY_CHECKS=0;

2) Globally

SET GLOBAL FOREIGN_KEY_CHECKS=0;

This error originally happens since we have few values in the referencing field of the child table, which do not subsist in the referenced/candidate field of the parent table.

Occasionally, we may obtain this error at the time we are employing Foreign Key constraints to existing table(s), having data in them meanwhile. Few of the other answers are indicating to delete the data fully from child table, and then employ the constraint. But, this is not an option at the time we meanwhile have working/production data in the child table. In most continuity, we will require to update the data in the child table (instead of deleting them).

Now, we can utilize Left link to trace all those rows in the child table, which does not have matching values in the parent table. Following query , would be useful to retrieve 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
  1. Based on your "business logic", you will require to update/match these unmatching value(s), with the subsisting values in the parent table. You may occasinally require to set them null as well.

  2. Delete these rows having unmatching values.

  3. Include new rows in your parent table, similar to the unmatching values in the child table.

Once the data is set, we can employ the Foreign key constraint using ALTER TABLE syntax.

Perhaps your sourcecodes_tags table contains sourcecode_id values that no longer subsists in your sourcecodes table. You have to get rid of those first.

Here's a query that can trace those IDs:

SELECT DISTINCT sourcecode_id FROM 
   sourcecodes_tags tags LEFT JOIN sourcecodes sc ON tags.sourcecode_id=sc.id 
WHERE sc.id IS NULL;

 

10 6 4
31,120 points

Related questions

0 votes
1 answer 16 views
16 views
Problem: How can I fix this issue:Error 1452: cannot add or update a child row: a foreign key constraint fails .
asked Mar 15 Wafa Abu Yousef 6.1k points
0 votes
2 answers 359 views
359 views
Problem : I have created the tables in MySQL Workbench. But when want to insert values into my TBL ORDRELINJE table I face below error : Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (srdjank.TBLOrdrelinje, CONSTRAINT TBLOrdrelinje_fk FOREIGN KEY ... do the research on this topic, but no luck. Am I doing any mistake or someone have any idea about what can I do?
asked Dec 21, 2019 alecxe 7.5k points
0 votes
1 answer 128 views
128 views
Problem : When I try to execute the below SQL command : INSERT INTO mytest_usershosts (RID,userid,hid,Usr,Pass) VALUES (NULL,1,1,"user","pass"); I am facing the below error message : ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f ... similar cases, but I am unable to find any solution. Is there anyone who have faced the same issue and knows the fix for the issue?
asked Dec 11, 2019 alecxe 7.5k points
0 votes
1 answer 3 views
3 views
Problem: table 1 +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------------- ... /table2`, CONSTRAINT `table2_ibfk_1` FOREIGN KEY (`UserID`) REFERENCES `table1` (`UserID`)) What have I done wrong? I read http://www.w3schools.com/Sql/sql_foreignkey.asp and I don't see what's wrong.
asked Apr 26 anika11 32.2k points
0 votes
1 answer 4 views
4 views
Problem: Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails.How to resolve this error?
asked Apr 21 Humaira ahmed 50.7k points
0 votes
1 answer 2 views
2 views
Problem: Error:com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`myapp/table2`, CONSTRAINT `table2_ibfk_1` FOREIGN KEY (`UserID`) REFERENCES `table1` (`UserID`)) What have I done wrong? I read http://www.w3schools.com/Sql/sql_foreignkey.asp and I don't see what's wrong.
asked 4 days ago sumaiya simi 43.9k points
1 vote
1 answer 6 views
6 views
Problem: How to figure out this in mYSQL? Error 1217 (23000): cannot delete or update a parent row: a foreign key constraint fails.
asked Apr 24 PkGuy 23.5k points
0 votes
1 answer 347 views
347 views
Problem : In the MySQL I am trying to drop the table named bericht. I tried many options however still facing the same error that my table cannot be dropped. I am facing following error message : #1217 - Cannot delete or update a parent row: a foreign key constraint fails Does anybody have any clue about this error #1217?
asked Dec 2, 2019 alecxe 7.5k points
1 vote
1 answer 3 views
3 views
Problem: I keep getting this error whenever I try to use the delete statement . Error: 1451 - Cannot delete or update a parent row: a foreign key constraint fails (paymesomething.advertisers, CONSTRAINT advertisers_ibfk_1 FOREIGN KEY (advertiser_id) REFERENCES jobs (advertiser_id)) ... (`advertiser_id`) REFERENCES `jobs` (`advertiser_id`); Why am I getting this error and how can I solve this ?
asked Apr 26 Raj Tyagi 14.9k points
0 votes
1 answer 41 views
41 views
Problem: I'm getting this error when trying to delete a user from the database, I know it's doing this because the user I'm trying to delete is a foreign key in the appointments table, but I don't know how to correct it or where I have gone ... FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`), ADD CONSTRAINT `appointments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);
asked Apr 30 muktaa 34.6k points