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
You should first insert the row to your
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
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 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:
LEFT JOIN parent_table
ON parent_table.referenced_column = child_table.referencing_column
WHERE parent_table.referenced_column IS NULL
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.
Delete these rows having unmatching values.
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.
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;