Search code examples
mysqlforeign-keys

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


I have 2 tables: RubricsDocument and Documents. In the RubricsDocument, I have the id of the document (foreign key). My goal is to delete one document but the error

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

appears. I have try to delete rubrics of the document before the document but isn't work. I don't know what i have to do to resolve this error.

http://sqlfiddle.com/#!9/204749 to see the creation of tables

RubricsDocument Table
-
1450    1   1   245 66
<-- 66 is id of the document    
1451    2   1   296 66    
1452    3   1   297 66    
1453    4   1   298 66    
1456    7   1   301 66

Solution

  • I have try to delete rubrics of the document before the document but isn't work

    It should work unless there is in this table a column that is also referenced by another table as a foreign key. When you first posted the CREATE statements in the comments, both tables had such references but the link you provided shows different statements.
    So if the link contains the actual CREATE statements you can delete the referenced rows first and then the row from NETENTDOC.
    But you could achieve what you want if you applied this: ON DELETE CASCADE to the FOREIGN KEY:

    CREATE TABLE `NETDOCRUB` (
        `NETIDDOCRUB` BIGINT(20) NOT NULL AUTO_INCREMENT,
        `NETNUPOS` BIGINT(2) NOT NULL,
        `NETFGVISIBLE` TINYINT(4) NOT NULL,
        `NETIDRUB` BIGINT(20) NOT NULL,
        `NETIDENTDOC` BIGINT(20) NOT NULL,
        PRIMARY KEY(`NETIDDOCRUB`),
        FOREIGN KEY (`NETIDENTDOC`) REFERENCES `NETENTDOC` (`NETIDENTDOC`) ON DELETE CASCADE
    );
    

    ON DELETE CASCADE takes care of the deletion of all "child" rows when a "parent" row is deleted.