Mysql error 1452 - Cannot add or update a child row a foreign key constraint fails

Question

I m having a bit of a strange problem  I m trying to add a foreign key to one table that references another  but it is failing for some reason  With my limited knowledge of MySQL  the only thing that could possibly be suspect is that there is a foreign key on a different table referencing the one I am trying to reference   I ve done a SHOW CREATE TABLE query on both tables  sourcecodes tags is the table with the foreign key  sourcecodes is the referenced table   CREATE TABLE  sourcecodes      id  int 11  unsigned NOT NULL AUTO INCREMENT    user id  int 11  unsigned NOT NULL    language id  int 11  unsigned NOT NULL    category id  int 11  unsigned NOT NULL    title  varchar 40  CHARACTER SET utf8 NOT NULL    description  text CHARACTER SET utf8 NOT NULL    views  int 11  unsigned NOT NULL    downloads  int 11  unsigned NOT NULL    time posted  timestamp NOT NULL DEFAULT CURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP   PRIMARY KEY   id     KEY  user id    user id     KEY  language id    language id     KEY  category id    category id     CONSTRAINT  sourcecodes ibfk 3  FOREIGN KEY   language id   REFERENCES  languages    id   ON DELETE CASCADE ON UPDATE CASCADE   CONSTRAINT  sourcecodes ibfk 1  FOREIGN KEY   user id   REFERENCES  users    id   ON DELETE CASCADE ON UPDATE CASCADE   CONSTRAINT  sourcecodes ibfk 2  FOREIGN KEY   category id   REFERENCES  categories    id   ON DELETE CASCADE ON UPDATE CASCADE   ENGINE InnoDB AUTO INCREMENT 4 DEFAULT CHARSET latin1  CREATE TABLE  sourcecodes tags      sourcecode id  int 11  unsigned NOT NULL    tag id  int 11  unsigned NOT NULL   KEY  sourcecode id    sourcecode id     KEY  tag id    tag id     CONSTRAINT  sourcecodes tags ibfk 1  FOREIGN KEY   tag id   REFERENCES  tags    id   ON DELETE CASCADE ON UPDATE CASCADE   ENGINE InnoDB DEFAULT CHARSET latin1   This is the code that generates the error   ALTER TABLE sourcecodes tags ADD FOREIGN KEY  sourcecode id  REFERENCES sourcecodes  id  ON DELETE CASCADE ON UPDATE CASCADE

User · Answer

For me  this problem was a little different and super easy to check and solve    You must ensure BOTH of your tables are InnoDB  If one of the tables  namely the reference table is a MyISAM  the constraint will fail       SHOW TABLE STATUS WHERE Name     t1        ALTER TABLE t1 ENGINE InnoDB

User · Answer

I had the same problem and found solution  placing NULL instead of NOT NULL on foreign key column  Here is a query   ALTER TABLE  db   table1  ADD COLUMN  col table2 fk  INT UNSIGNED NULL  ADD INDEX  col table2 fk idx    col table2 fk  ASC   ADD CONSTRAINT  col table2 fk1  FOREIGN KEY   col table2 fk   REFERENCES  db   table2    table2 id   ON DELETE NO ACTION ON UPDATE NO ACTION    MySQL has executed this query

User · Answer

I end up delete all the data in my table  and run alter again  It works  Not the brilliant one  but it save a lot time  especially your application is still in development stage without any customer data

User · Answer

UPDATE sourcecodes tags SET sourcecode id   NULL WHERE sourcecode id NOT IN     SELECT id FROM sourcecodes     should help to get rid of those IDs  Or if null is not allowed in sourcecode id  then remove those rows or add those missing values to the sourcecodes table

User · Answer

Empty both your tables  data and run the command  It will work

User · Answer

I had the same issue with my MySQL database but finally  I got a solution which worked for me  Since in my table everything was fine from the mysql point of view both tables should use InnoDB engine and the datatype of each column should be of the same type which takes part in foreign key constraint   The only 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

User · Answer

I d the same problem  I checked rows of my tables and found there was some incompatibility with the value of fields that I wanted to define a foreign key  I corrected those value  tried again and the problem was solved

User · Answer

Use NOT IN to find where constraints are constraining   SELECT column FROM table WHERE column NOT IN   SELECT intended foreign key FROM another table    so  more specifically   SELECT sourcecode id FROM sourcecodes tags WHERE sourcecode id NOT IN   SELECT id FROM sourcecodes    EDIT  IN and NOT IN operators are known to be much faster than the JOIN operators  as well as much easier to construct  and repeat

User · Answer

I had the same problem today  I tested for four things  some of them already mentioned here    Are there any values in your child column that don t exist in the parent column  besides NULL  if the child column is nullable  Do child and parent columns have the same datatype  Is there an index on the parent column you are referencing  MySQL seems to require this for performance reasons  http   dev mysql com doc refman 5 5 en create-table-foreign-keys html  And this one solved it for me  Do both tables have identical collation     I had one table in UTF-8 and the other in iso-something  That didn t work  After changing the iso-table to UTF-8 collation the constraints could be added without problems  In my case  phpMyAdmin didn t even show the child table in iso-encoding in the dropdown for creating the foreign key constraint

User · Answer

Make sure the value is in the other table otherwise you will get this error  in the assigned corresponding column   So if it is assigned column is assigned to a row id of another table   make sure there is a row that is in the table otherwise this error will appear

User · Answer

Quite likely your sourcecodes tags table contains sourcecode id values that no longer exists in your sourcecodes table  You have to get rid of those first   Here s a query that can find 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

User · Answer

I was getting this error when using Laravel and eloquent  trying to make a foreign key link would cause a 1452  The problem was lack of data in the linked table   Please see here for an example  http   mstd eu index php 2016 12 02 laravel-eloquent-integrity-constraint-violation-1452-foreign-key-constraint

User · Answer

try this  SET foreign key checks   0   ALTER TABLE sourcecodes tags ADD FOREIGN KEY  sourcecode id  REFERENCES sourcecodes  id  ON DELETE CASCADE ON UPDATE CASCADE  SET foreign key checks   1

User · Answer

I had this exact same problem about three different times  In each instance it was because one  or more  of my records did not conform to the new foreign key  You may want to update your existing records to follow the syntax constraints of the foreign key before trying to add the key itself  The following example should generally isolate the problem records   SELECT   FROM  tablename      WHERE  candidate key   lt  gt   proposed foreign key value           AND  candidate key   lt  gt   next proposed foreign key value    repeat AND  candidate key   lt  gt   next proposed foreign key value  within your query for each value in the foreign key   If you have a ton of records this can be difficult  but if your table is reasonably small it shouldn t take too long  I m not super amazing in SQL syntax  but this has always isolated the issue for me

User · Answer

You just need to answer one question   Is your table already storing data   Especially the table included foreign key    If the answer is yes  then the only thing you need to do is to delete all the records  then you are free to add any foreign key to your table      Delete instruction  From child which include foreign key table  to parent table    The reason you cannot add in foreign key after data entries is due to the table inconsistency  how are you going to deal with a new foreign key on the former data-filled the table   If the answer is no  then follow other instructions

User · Answer

This also happens when setting a foreign key to parent id to child column if the child column has a value of 0 already and no parent id value is 0  You would need to ensure that each child column is NULL or has value that exists in parent id  And now that I read the statement nos wrote  that s what he is validating

User · Answer

In my case  I created a new table with the same structure  created the relationships with the other tables  then extracted the data in CSV from the old table that has the problem  then imported the CSV to the new table and disabled foreign key checking and disabled import interruption  all my data are inserted to the new table that has no problem successfully  then deleted the old table   It worked for me

User · Answer

you can try this exapmple   START TRANSACTION   SET foreign key checks   0   ALTER TABLE  job definers  ADD CONSTRAINT  job cities foreign  FOREIGN KEY     job cities   REFERENCES  drop down lists   id   ON DELETE CASCADE ON UPDATE CASCADE   SET foreign key checks   1   COMMIT    Note   if you are using phpmyadmin just uncheck Enable foreign key checks  as example    hope this soloution fix your problem

User · Answer

It seems there is some invalid value for the column line 0 that is not a valid foreign key so MySQL cannot set a foreign key constraint for it   You can follow these steps    Drop the column which you have tried to set FK constraint for  Add it again and set its default value as NULL  Try to set a foreign key constraint for it again

User · Answer

I was readying this solutions and this example may help   My database have two tables  email and credit card  with primary keys for their IDs  Another table  client  refers to this tables IDs as foreign keys  I have a reason to have the email apart from the client data   First I insert the row data for the referenced tables  email  credit card  then you get the ID for each  those IDs are needed in the third table  client     If you don t insert first the rows in the referenced tables  MySQL wont be able to make the correspondences when you insert a new row in the third table that reference the foreign keys   If you first insert the referenced rows for the referenced tables  then the row that refers to foreign keys  no error occurs   Hope this helps

User · Answer

Truncate the tables and then try adding the FK Constraint  I know this solution is a bit awkward but it does work 100   But I agree that this is not an ideal solution to deal with problem  but I hope it helps

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

Examples related to mysql

Examples related to foreign-keys

Examples related to mysql-error-1452