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