• Register
0 votes
248 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 (Ordre) REFERENCES TBLOrdre (OrdreID))

I have tried to do the research on this topic, but no luck. Am I doing any mistake or someone have any idea about what can I do?

6 5 3
7,540 points

Please log in or register to answer this question.

2 Answers

0 votes

Solution :

The Foreign key relationships usually involve the parent table which holds the central data values, and the child table with the identical values  those are pointing back to its parent. The FOREIGN KEY clause is mainly specified in the child table.

So it must reject any INSERT or UPDATE operation that is trying  to attempt to create the foreign key value in the child table if there is not the matching candidate key value in your parent table.

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

So you must first insert that row to your TBLOrdre table.

9 7 4
38,600 points
0 votes

Solution:

Foreign key relationships employ a parent table that contains the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is fixed 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 getting this constraint check since Ordre table does not have reference OrdreID provided in insert command.

To insert value in Ordrelinje, you first should enter value in Ordre table and employ same OrdreID in Orderlinje table.

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

You should 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 fresh data having the similar foreign key value as the primary key in the parent table . 

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

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

Perform 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;

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

Occasionnally, we may get 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 exposing 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).

Currently, 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 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

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

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

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

Apparently your sourcecodes_tags table contains sourcecode_id values that no longer subsists in your sourcecodes table. You have to exclude 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;

I had the similar problem with my MySQL database however finally, I got a solution which performed for me.
Because in my table everything was fine from the mysql point of view(both tables should use InnoDB engine and the datatype of each column must be of the same type which takes part in foreign key constraint).

The single thing that I did was to disable the foreign key check and later on enabled it after performing the foreign key operation.
Steps that I took:

SET foreign_key_checks = 0;
alter table tblUsedDestination add constraint f_operatorId foreign key(iOperatorId) references tblOperators (iOperatorId); Query
OK, 8 rows affected (0.23 sec) Records: 8  Duplicates: 0  Warnings: 0
SET foreign_key_checks = 1;

 

10 6 4
31,120 points

Related questions

0 votes
2 answers 853 views
853 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 ... (OrdreID)) I have seen many other posts on this topic but unable to fix my issue. How can I fix this issue?
asked Jan 24, 2020 jwilliam 3.9k points
0 votes
1 answer 65 views
65 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 209 views
209 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
0 votes
1 answer 18 views
18 views
Problem: I am unable to understand why a TRUNCATE on mygroup is not working? Even though I have it ON DELETE CASCADE SET I encounter below strange error: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, ...    DateTime     DATETIME DEFAULT NULL, FOREIGN KEY  (GroupID) REFERENCES mygroup(ID) ON DELETE CASCADE,    UNIQUE(GroupID) ) ENGINE=InnoDB;
asked Jul 23, 2020 Raphael Pacheco 4.9k points
0 votes
1 answer 184 views
184 views
I am facing this problems in many times and hardly getting the soluton. Can someone here help me to solve this issue?
asked Sep 14, 2020 Daniel Anderson 4k points
0 votes
1 answer 2.4K views
2.4K views
Problem : Following is my issue: I tried to install mysqlclient like below : C:\Users\alecxe>pip install mysqlclient Collecting mysqlclient Using cached https://files.pythonhosted.org/packages/ec/fd/83329b9d3e14f7344d1 cb31f128e6dbba70c5975c9e57896815dbb1988ad/mysqlclient-1.3.13.tar.gz Installed ... beginner to this and would be very happy if any input as to how to go about fixing above error.
asked Dec 27, 2019 alecxe 7.5k points
0 votes
1 answer 60 views
60 views
Problem : I am very new to the php and I am trying from long time but I cannot sort out the issue with my below code. Please let me know how can I fix the error? "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' '0')' at line 1"
asked Feb 3, 2020 jwilliam 3.9k points
0 votes
1 answer 25 views
25 views
Problem: I am able to create the table named employee which contains the column empid which is a computed column and empid column is the primary key also. It is the self referencing table and the column managerid references back to itself. But now if I try to save ... error my table is still working perfectly. I am trying to work in a table graphically not coding every time I make the change.
asked Jul 4, 2020 Raphael Pacheco 4.9k points
0 votes
1 answer 64 views
64 views
Problem : I am trying to import mySql database which was originally an access database into phpmyadmin and its giving me following error: incorrect table definition; there can be only one auto column and it must be defined as a key
asked Nov 14, 2019 peterlaw 6.9k points
0 votes
1 answer 7 views