Integrity constraint violation 1452 Cannot add or update a child row

Question

I am trying to insert values into my comments table and I am getting a error  Its saying that I can not add or update child row and I have no idea what that means   my schema looks something like this  -- ---------------------------- -- Table structure for  comments  -- ---------------------------- DROP TABLE IF EXISTS  comments   CREATE TABLE  comments       id  varchar 36  NOT NULL     project id  varchar 36  NOT NULL     user id  varchar 36  NOT NULL     task id  varchar 36  NOT NULL     data type id  varchar 36  NOT NULL     data path  varchar 255  DEFAULT NULL     message  longtext     created  datetime DEFAULT NULL     modified  datetime DEFAULT NULL    PRIMARY KEY   id      KEY  fk comments users    user id      KEY  fk comments projects1    project id      KEY  fk comments data types1    data type id      CONSTRAINT  fk comments data types1  FOREIGN KEY   data type id   REFERENCES  data types    id   ON DELETE NO ACTION ON UPDATE NO ACTION    CONSTRAINT  fk comments projects1  FOREIGN KEY   project id   REFERENCES  projects    id   ON DELETE NO ACTION ON UPDATE NO ACTION    CONSTRAINT  fk comments users  FOREIGN KEY   user id   REFERENCES  users    id   ON DELETE NO ACTION ON UPDATE NO ACTION   ENGINE InnoDB DEFAULT CHARSET utf32   -- ---------------------------- -- Records of comments -- ----------------------------  -- ---------------------------- -- Table structure for  projects  -- ---------------------------- DROP TABLE IF EXISTS  projects   CREATE TABLE  projects       id  varchar 36  NOT NULL     user id  varchar 36  NOT NULL     title  varchar 45  DEFAULT NULL     description  longtext     created  datetime DEFAULT NULL     modified  datetime DEFAULT NULL    PRIMARY KEY   id      KEY  fk projects users1    user id      CONSTRAINT  fk projects users1  FOREIGN KEY   user id   REFERENCES  users    id   ON DELETE NO ACTION ON UPDATE NO ACTION   ENGINE InnoDB DEFAULT CHARSET utf32   -- ---------------------------- -- Records of projects -- ---------------------------- INSERT INTO  projects  VALUES   50dcbc72-3410-4596-8b71-0e80ae7aaee3    50dcbc5c-d684-40bf-9715-0becae7aaee3    Brand New Project    This is a brand new project    2012-12-27 15 24 02    2012-12-27 15 24 02      and the mysql statement I am trying to do looks something like this  INSERT INTO  anthonyl fbpj   comments    project id    user id    task id    data type id    message    modified    created    id    VALUES   50dc845a-83e4-4db3-8705-5432ae7aaee3    50dcbc5c-d684-40bf-9715-0becae7aaee3    1    50d32e5c-abdc-491a-a0ef-25d84e9f49a8    this is a test    2012-12-27 19 20 46    2012-12-27 19 20 46    50dcf3ee-8bf4-4685-aa45-4eb4ae7aaee3     the error I get looks like this     SQLSTATE 23000   Integrity constraint violation  1452 Cannot add or   update a child row  a foreign key constraint fails    anthonyl fbpj comments  CONSTRAINT fk comments projects1   FOREIGN KEY  project id  REFERENCES projects  id  ON DELETE NO   ACTION ON UPDATE NO ACTION

User · Answer

Maybe you have some rows in the table that you want to create de FK   Run the migration with foreign key checks OFF Insert only those records that have corresponding id field in contents table

User · Answer

I had the same error  the problem was that I was trying to add role id foreign to the users table  but role id did not have a default value  so the DB did not allow me to insert the column because I already had some users and it didn t know how to add the column to them  Since I was in development I just used migrate fresh  but if I was on production  I would probably set a default value to the role id and not make it not constrained until I had the corresponding role on the DB

User · Answer

In case someone is using Laravel and is getting this problem  I was getting this as well and the issue was in the order in which I was inserting the ids  i e   the foreign keys  in the pivot table   To be concrete  find below an example for a many to many relationship   wordtokens  lt -  wordtoken wordchunk  lt -  wordchunks     wordtoken wordchunk table Schema  create  wordtoken wordchunk   function Blueprint  table             table- gt integer  wordtoken id  - gt unsigned             table- gt integer  wordchunk id  - gt unsigned              table- gt foreign  wordtoken id  - gt references  id  - gt on  word tokens  - gt onDelete  cascade             table- gt foreign  wordchunk id  - gt references  id  - gt on  wordchunks  - gt onDelete  cascade              table- gt primary   wordtoken id    wordchunk id                 wordchunks table Schema  create  wordchunks   function  Blueprint  table             table- gt increments  id             table- gt timestamps             table- gt string  text                wordtokens table Schema  create  word tokens   function  Blueprint  table             table- gt increments  id             table- gt string  text          Now my models look like follows   class WordToken extends Model      public function wordchunks           return  this- gt belongsToMany  App Wordchunk            class Wordchunk extends Model        public function wordTokens             return  this- gt belongsToMany  App WordToken    wordtoken wordchunk    wordchunk id    wordtoken id              I fixed the problem by exchanging the order of  wordchunk id  and  wordtoken id  in the Wordchunk model   For code completion  this is how I persist the models   private function persistChunks  chunks        foreach   chunks as  chunk             model   new Wordchunk             model- gt text   implode      array map function  token   return  token- gt text     chunk             tokenIds   array map function  token   return  token- gt id     chunk            model- gt save             model- gt wordTokens  - gt attach  tokenIds

User · Answer

Make sure you have project id in the fillable property of your Comment model   I had the same issue  And this was the reason

User · Answer

It just simply means that the value for column project id on table comments you are inserting doesn t exist on table projects  Bear in mind that the values of column project id on table comments is dependent on the values of ID on table Projects   The value 50dc845a-83e4-4db3-8705-5432ae7aaee3 you are inserting for column project id does not exist on table projects

User · Answer

First delete the constraint  fk comments projects1  and also its index  After that recreate it

User · Answer

Also make sure that the foreign key you add is the same type of the original column  if the column you re reference is not the same type it will fail too

User · Answer

I had this issue when I was accidentally using the WRONG  uuid  in my child record  When that happens the constraint looks from the child to the parent record to ensure that the link is correct  I was generating it manually  when I had already rigged my Model to do it automatically  So my fix was    parent   Parent create  recData      asssigning autogenerated uuid into  parent   Then when I called my child class to insert children  I passed this var value    parent- gt uuid   Hope that helps

User · Answer

If you are adding new foreign key to an existing table and the columns are not null and not assigned default value  you will get this error   Either you need to make it nullable or assign default value  or delete all the existing records to solve it

User · Answer

I just exported the table deleted and then imported it again and it worked for me  This was because i deleted the parent table users  and then recreated it and child table likes  has the foreign key to parent table users

User · Answer

You also get this error if you do not create and populate your tables in the right order  For example  according to your schema  Comments table needs user id  project id  task id and data type id  This means that Users table  Projects table  Task table and Data Type table must already have exited and have values in them before you can reference their ids or any other column  In Laravel this would mean calling your database seeders in the right order  class DatabaseSeeder extends Seeder                  Seed the application s database                 return void             public function run                  this- gt call UserSeeder  class            this- gt call ProjectSeeder  class            this- gt call TaskSeeder  class            this- gt call DataTypeSeeder  class            this- gt call CommentSeeder  class            This was how I solved a similar issue

User · Answer

I hope my decision will help  I had a similar error in Laravel  I added a foreign key to the wrong table  Wrong code   Schema  create  comments   function  Blueprint  table     table- gt unsignedBigInteger  post id  - gt index  - gt nullable         table- gt foreign  post id  - gt references  id  - gt on  comments  - gt onDelete  cascade              Schema  create  posts   function  Blueprint  table         table- gt bigIncrements  id                      Please  note to the function on  comments   above  Correct code    table- gt foreign  post id  - gt references  id  - gt on  posts  - gt onDelete  cascade

User · Answer

that means that the value for column project id on table comments you are inserting not only doesn t exist on table projects BUT  also  project id probably doesn t have default value  E g  in my case I set it as NULL  As for  Laravel you can consider this expressions as a chunk of code of a  migration php file  for example  class ForeinToPhotosFromUsers extends Migration          Run the migrations       return void    public function up         Schema  table  users   function  Blueprint  table         table- gt unsignedBigInteger  photo id  - gt nullable            THIS STRING            table- gt foreign  photo id  - gt references  id  - gt on  photos             Obviously you had to create the Model class in my case it was Photo  next to all these

[mysql] Integrity constraint violation: 1452 Cannot add or update a child row:

Examples related to mysql

Examples related to sql