Cannot delete or update a parent row a foreign key constraint fails

Question

When doing   DELETE FROM  jobs  WHERE  job id   1 LIMIT 1    It errors    1451 - Cannot delete or update a parent row  a foreign key constraint fails   paymesomething advertisers  CONSTRAINT advertisers ibfk 1 FOREIGN KEY   advertiser id  REFERENCES jobs  advertiser id     Here are my tables   CREATE TABLE IF NOT EXISTS  advertisers       advertiser id  int 11  unsigned NOT NULL AUTO INCREMENT     name  varchar 255  NOT NULL     password  char 32  NOT NULL     email  varchar 128  NOT NULL     address  varchar 255  NOT NULL     phone  varchar 255  NOT NULL     fax  varchar 255  NOT NULL     session token  char 30  NOT NULL    PRIMARY KEY   advertiser id      UNIQUE KEY  email    email     ENGINE InnoDB  DEFAULT CHARSET utf8 AUTO INCREMENT 2     INSERT INTO  advertisers    advertiser id    name    password    email    address    phone    fax    session token   VALUES  1   TEST COMPANY                             CREATE TABLE IF NOT EXISTS  jobs       job id  int 11  unsigned NOT NULL AUTO INCREMENT     advertiser id  int 11  unsigned NOT NULL     name  varchar 255  NOT NULL     shortdesc  varchar 255  NOT NULL     longdesc  text NOT NULL     address  varchar 255  NOT NULL     time added  int 11  NOT NULL     active  tinyint 1  NOT NULL     moderated  tinyint 1  NOT NULL    PRIMARY KEY   job id      KEY  advertiser id    advertiser id   active   moderated     ENGINE InnoDB  DEFAULT CHARSET utf8 AUTO INCREMENT 2     INSERT INTO  jobs    job id    advertiser id    name    shortdesc    longdesc    address    active    moderated   VALUES  1  1   TEST    TESTTEST    TESTTESTES       0  0    ALTER TABLE  advertisers    ADD CONSTRAINT  advertisers ibfk 1  FOREIGN KEY   advertiser id   REFERENCES  jobs    advertiser id

User · Answer

Maybe you should try ON DELETE CASCADE

User · Answer

Under your current  possibly flawed  design  you must delete the row out of the advertisers table before you can delete the row in the jobs table that it references   Alternatively  you could set up your foreign key such that a delete in the parent table causes rows in child tables to be deleted automatically  This is called a cascading delete  It looks something like this   ALTER TABLE  advertisers  ADD CONSTRAINT  advertisers ibfk 1  FOREIGN KEY   advertiser id   REFERENCES  jobs    advertiser id   ON DELETE CASCADE    Having said that  as others have already pointed out  your foreign key feels like it should go the other way around since the advertisers table really contains the primary key and the jobs table contains the foreign key  I would rewrite it like this   ALTER TABLE  jobs  ADD FOREIGN KEY   advertiser id   REFERENCES  advertisers    advertiser id      And the cascading delete won t be necessary

User · Answer

Disable the foreign key check and make the changes then re-enable foreign key check  SET FOREIGN KEY CHECKS 0  -- to disable them DELETE FROM  jobs  WHERE  job id    1 LIMIT 1  SET FOREIGN KEY CHECKS 1  -- to re-enable them

User · Answer

If you want to drop a table you should execute the following query in a single step  SET FOREIGN KEY CHECKS 0   DROP TABLE table name

User · Answer

The main problem with this erorr Error Code  1451  Cannot delete or update a parent row  a foreign key constraint fails is that it doesn t let you know which table contains the FK failure  so it is difficult to solve the conflict   If you use MySQL or similar  I found out that you can create an ER diagram for your database  then you can review and safely remove any conflicts triggering the error     Use MySQL workbench Click on Database -  Reverse Engineering Select a correct connection Next till the end  remember to select database  amp  tables that need examine Now you have the ER diagram  you can see which table have FK conflict

User · Answer

When you create database or create tables   You should add that line at top script create database or table  SET  OLD FOREIGN KEY CHECKS   FOREIGN KEY CHECKS  FOREIGN KEY CHECKS 1    Now you want to delete records from table  then you write as  SET  OLD FOREIGN KEY CHECKS   FOREIGN KEY CHECKS  FOREIGN KEY CHECKS 1  DELETE FROM  jobs  WHERE  job id   1 LIMIT 1   Good luck

User · Answer

I think that your foreign key is backwards  Try   ALTER TABLE  jobs  ADD CONSTRAINT  advertisers ibfk 1  FOREIGN KEY   advertiser id   REFERENCES  advertisers    advertiser id

User · Answer

You could create a trigger to delete the referenced rows in before deleting the job       DELIMITER        CREATE TRIGGER before jobs delete          BEFORE DELETE ON jobs         FOR EACH ROW      BEGIN         delete from advertisers where advertiser id OLD advertiser id      END       DELIMITER

User · Answer

I had this  problem in laravel migration too  the order of drop tables in down   method does matter  Schema  dropIfExists  groups    Schema  dropIfExists  contact      may not work  but if you change the order  it works   Schema  dropIfExists  contact    Schema  dropIfExists  groups

User · Answer

How about this alternative I ve been using  allow the foreign key to be NULL and then choose ON DELETE  SET NULL   Personally I prefer using both  ON UPDATE CASCADE  as well as  ON DELETE SET NULL  to avoid unnecessary complications  but on your set up you may want a different approach  Also  NULL ing foreign key values may latter lead complications as you won t know what exactly happened there  So this change should be in close relation to how your application code works   Hope this helps

User · Answer

You need to delete it by order  There are dependency in the tables

User · Answer

The simple way would be to disable the foreign key check  make the changes then re-enable foreign key check   SET FOREIGN KEY CHECKS 0  -- to disable them SET FOREIGN KEY CHECKS 1  -- to re-enable them

User · Answer

As is  you must delete the row out of the advertisers table before you can delete the row in the jobs table that it references   This   ALTER TABLE  advertisers    ADD CONSTRAINT  advertisers ibfk 1  FOREIGN KEY   advertiser id          REFERENCES  jobs    advertiser id         is actually the opposite to what it should be   As it is  it means that you d have to have a record in the jobs table before the advertisers   So you need to use   ALTER TABLE  jobs    ADD CONSTRAINT  advertisers ibfk 1  FOREIGN KEY   advertiser id          REFERENCES  advertisers    advertiser id      Once you correct the foreign key relationship  your delete statement will work

User · Answer

I tried the solution mentioned by  Alino Manzi but it didn t work for me on the WordPress related tables using wpdb   then I modified the code as below and it worked  SET FOREIGN KEY CHECKS OFF    disabling foreign key    run the queries which are giving foreign key errors  SET FOREIGN KEY CHECKS ON     enabling foreign key

User · Answer

This happened to me as well and due to a dependency and reference from other tables  I could not remove the entry  What I did is added a delete column  of type boolean  to the table  The value in that field showed whether the item is marked for deletion or not  If marked for deletion  don t fetch use  otherwise  use it

User · Answer

If there are more than one job having the same advertiser id  then your foreign key should be   ALTER TABLE  jobs  ADD CONSTRAINT  advertisers ibfk 1   FOREIGN KEY   advertiser id    REFERENCES  advertisers    advertiser id      Otherwise  if its the other way round in your case   if you want the rows in advertiser to be automatically deleted if the row in job is deleted add the  ON DELETE CASCADE  option to the end of your foreign key   ALTER TABLE  advertisers  ADD CONSTRAINT  advertisers ibfk 1   FOREIGN KEY   advertiser id    REFERENCES  jobs    advertiser id   ON DELETE CASCADE    Check out Foreign Key constraints

User · Answer

Basically  The reason behind these type of error is eventually you are trying to delete a tupple which has primary key  root table   amp  that  primary key is used in child table as a foreign key   In this scenario in order to delete parent table data you have to remove child table data  in which foreign key is used   Thanks

User · Answer

if you need to support client as soon as possible  and do not have access to   FOREIGN KEY CHECKS   so that data integrity can be disabled   1  delete foreign key  ALTER TABLE  advertisers   DROP FOREIGN KEY  advertisers ibfk 1     2  activate your deleting operation thruogh sql or api  3  add the foreign key back to schema  ALTER TABLE  advertisers    ADD CONSTRAINT  advertisers ibfk 1  FOREIGN KEY   advertiser id   REFERENCES  jobs    advertiser id      however  it is a hot-fix  so it is on your own risk  because the main flaw of such approach is that it is needed afterwards to keep the data integrity manually

[mysql] Cannot delete or update a parent row: a foreign key constraint fails

Examples related to mysql

Examples related to sql