none
SQL error msg - The DELETE statement conflicted with the SAME TABLE REFERENCE constraint

    Question

  • Executed as user: ****. The DELETE statement
    conflicted with the SAME TABLE REFERENCE constraint "FK_PARENT_TASK_REF".
    The conflict occurred in database "****", table "****", column
    'PARENT_TASK_ID'. [SQLSTATE 23000] (Error 547) The statement has been
    terminated. [SQLSTATE 01000] (Error 3621). The step failed.

    Does this error msg indicate the whole script failed to execute or was it just a single step/task that failed ?

    What does error msg mean ?

    Anyway to prevent this error msg and ensure script runs successfully

    Sunday, July 28, 2013 11:44 AM

Answers

  • Hi mdavidh,

    This error occurs because the record  'PARENT_TASK_ID' was referenced by 'FK_PARENT_TASK_REF'.

    Please refer below codes:

    CREATE TABLE MyTable (
        ID    INT, primary key(ID),       -- primary key
        ID_Parent INT foreign key(ID_Parent) references MyTable(ID),  -- foreign key reference the same table   
        
    )
    insert into MyTable(ID,ID_Parent)
    values(0,0);
    insert into MyTable(ID,ID_Parent)
    values(1,0);
    insert into MyTable(ID,ID_Parent)
    values(2,0);
    insert into MyTable(ID,ID_Parent)
    values(3,1);
    insert into MyTable(ID,ID_Parent)
    values(4,3);
    insert into MyTable(ID,ID_Parent)
    values(5,4);
    CREATE TRIGGER MyTrigger
    on MyTable
    instead of delete
    as
        set nocount on
        update MyTable set ID_Parent = null where ID_Parent in (select ID from deleted)
        delete from MyTable where ID in (select ID from deleted)

    Now we could delete records.

    delete  from MyTable where ID_Parent=0

    Thanks,

    Candy Zhou


    Monday, July 29, 2013 12:12 PM

All replies

  • If you show us the whole script we will able to tell you. http://www.sommarskog.se/error-handling-I.html#scope-abortion

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, July 28, 2013 12:53 PM
  • Hi mdavidh,

    This error occurs because the record  'PARENT_TASK_ID' was referenced by 'FK_PARENT_TASK_REF'.

    Please refer below codes:

    CREATE TABLE MyTable (
        ID    INT, primary key(ID),       -- primary key
        ID_Parent INT foreign key(ID_Parent) references MyTable(ID),  -- foreign key reference the same table   
        
    )
    insert into MyTable(ID,ID_Parent)
    values(0,0);
    insert into MyTable(ID,ID_Parent)
    values(1,0);
    insert into MyTable(ID,ID_Parent)
    values(2,0);
    insert into MyTable(ID,ID_Parent)
    values(3,1);
    insert into MyTable(ID,ID_Parent)
    values(4,3);
    insert into MyTable(ID,ID_Parent)
    values(5,4);
    CREATE TRIGGER MyTrigger
    on MyTable
    instead of delete
    as
        set nocount on
        update MyTable set ID_Parent = null where ID_Parent in (select ID from deleted)
        delete from MyTable where ID in (select ID from deleted)

    Now we could delete records.

    delete  from MyTable where ID_Parent=0

    Thanks,

    Candy Zhou


    Monday, July 29, 2013 12:12 PM
  • Hi,

    Why is it not possible to set the delete rule for this relationship? There are three options for delete rule. I understand that it is not possible to set the "cascade" delete because of loops and chaining. But why the other options are not allowed? Especially the "SET NULL" one. Right now I have to do this manually, find the child records and set the foreign key column on null. After that I can delete the parent. Why is it not possible to set the rule for it?

    Thanks!


    • Edited by mattti84 Friday, March 27, 2015 8:50 AM
    Friday, March 27, 2015 8:49 AM