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

User · Answer

I was getting this issue even though my parent table had all the values I was referencing in my child table. The issue seemed to be that I could not add multiple child references to a single foreign key. In other words if I had five rows of data referenced the same foreign key, MySQL was only allowing me to upload the first row and giving me the error 1452.

What worked for me was typing the code "SET GLOBAL FOREIGN_KEY_CHECKS=0". After that I closed out of MySQL and then restarted it and I was able to upload all of my data with no errors. I then typed "SET GLOBAL FOREIGN_KEY_CHECKS=1" to set the system back to normal although I'm not entirely sure what FOREIGN_KEY_CHECKS does. Hope this helps!

User · Answer

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  quot business logic quot   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

User · Answer

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

User · Answer

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

User · Answer

The answer of your question is that you must set the same value in Primary and secondary key  Thanks

User · Answer

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

User · Answer

When you re using foreign key  your order of columns should be same for insertion   For example  if you re adding  userid  password  in table1 from table2 then from table2 order should be same  userid  password  and not like  password userid  where userid is foreign key in table2 of table1

User · Answer

you should add data from REFERENCES KEY in PRIMARY TABLE to FOREIGN KEY in CHILD TABLE it means do not add random  data to foreign key   just use data from primary key that is accessable  description of data in foreign key

User · Answer

in the foreign key table has a value that is not owned in the primary key table that will be related  so you must delete all data first   adjust the value of your foreign key table according to the value that is in your primary key

User · Answer

I know this thread is long already but when I ran a laravel seeding for category tables it created some strange ids which were 1  11  21  31 which caused me error as I tried to reference it in another another table using ids like 1  2  3     the whole day until I checked the database up manually  So sometimes go look up the database table to ensure the foreign data actually exist properly  Cheers

User · Answer

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

User · Answer

check the no  of record in parent table that matches with child table and also primary key must match with foreign key reference  This works for me

User · Answer

ill squeeze this in here  my case was trying to create a like for a post which dint exist  while committing to database the  error was raised  solution was to first create the post then like it   from my understanding if the post id was to be saved in the likes table it had to first check with posts table to ascertain existence  i found it better to have it this way since its more logical to me that way

User · Answer

I am having the same issue here is my scenario i put empty     where value is NULL now this    value does not match with the parent table s id here is things need to check   all value with presented in parent table otherwise remove data from parent table then try

User · Answer

you should insert at least one raw in each tables  the ones you want the foreign keys pointing at  then you can insert or update the values of the foreign keys

User · Answer

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

User · Answer

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   Hope I made it clear

User · Answer

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

User · Answer

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 same as the parent table column even the engine and collation should also be the same  TRY THIS  This is how I solved mine  Correct me if am wrong

User · Answer

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

User · Answer

The problem occurs because you set the foreign key in child table after you insert some data in the child table   Try removing all the data from child table  then set the foreign key and afterwards add insert the data in table  it will work

User · Answer

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   Here also a youtube video

User · Answer

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

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

Examples related to mysql

Examples related to sql

Examples related to mariadb

Examples related to mysql-error-1452