Self-Referencing Constraint in Ms SQL

Self-referencing constraint in MS SQL

It is the case that you cannot set up ON DELETE CASCADE on a table with self-referencing constraints. There is a potential of cyclical logic problems, hence it won't allow it.

There's a good article here - though it's for version 8 rather than 9 of SQL - though the same rules apply.

using self referencing in sql server

The column references itself.

So the addition of the row itself guarantees that there is a matching row. This constraint can never fail.

In fact looking at the execution plan SQL Server realises this and doesn't even bother checking it. There is no assert operator present.

If we create a more typical Employee table there are different plans for the inserts that can violate the constraint as below.

create table EMP2(Eid int primary key, boss_id int null);
alter table EMP2 add constraint fk_EMP2_Eid 
 foreign key (boss_id) references EMP2(Eid)

insert into EMP2 values(1,null) /*Can't violate constraint as NULL*/
insert into EMP2 values(2,1)    /*Can violate constraint as NOT NULL*/

If you try multiple rows a blocking spool is added to the plan so the constraints aren't checked until all rows are inserted.

insert into EMP2 values (3,2),(4,3) /*Can violate constraint - multiple rows*/

And just for completeness as it was raised in the comments, looking at the case when the insert is to a table with a FK referencing a different one...

CREATE TABLE EmpSalaryHistory
(
Eid INT NOT NULL REFERENCES EMP(Eid),
EffectiveDate DATETIME NOT NULL,
Salary INT,
PRIMARY KEY (Eid,EffectiveDate)
)

INSERT INTO EmpSalaryHistory
VALUES    (1,GETDATE(),50000),
          (2,GETDATE(),50000)

In this instance no spool is added to the plan it can check as it inserts each row rather than all at the end so it can rollback earlier in the event that a row fails (the end result will be the same)

Creating a self referencing table

I think you've got the relationship reversed. The ProductTypeIDcolumn should be on the left under the "Primary key table" and the ProductTypeParentID column should be on the right under the "Foreign key table."

On delete cascade for self-referencing table

Assuming you're keeping your FOREIGN KEY constraint in place, you cannot fix the issue in a FOR DELETE trigger. FOR triggers (also known as AFTER triggers) fire after the activity has taken place. And a foreign key will prevent a row from being deleted if it has references. Foreign key checks occur before deletion.

What you need is an INSTEAD OF trigger. You also need to bear in mind that your current trigger only tried to deal with one "level" of referencing. (So, if row 3 references row 2 and row 2 references row 1, and you delete row 1, your trigger only tried to remove row 2)

So, something like:

CREATE TRIGGER [dbo].[T_comment_Trigger]
    ON [dbo].[Comments]
    INSTEAD OF DELETE
AS
    ;WITH IDs as (
       select id from deleted
       union all
       select c.id
       from Comments c
              inner join
            IDs i
              on
                 c.ParentID = i.id
    )
    DELETE FROM Comments
    WHERE id in (select id from IDs);

If there are other (non-self-referencing) cascading foreign key constraints, they all have to be replaced by actions in this trigger. In such a case, I'd recommend introducing a table variable to hold the list of all IDs that will eventually be deleted from the Comments table:

CREATE TRIGGER [dbo].[T_comment_Trigger]
    ON [dbo].[Comments]
    INSTEAD OF DELETE
AS
    declare @deletions table (ID varchar(7) not null);
    ;WITH IDs as (
       select id from deleted
       union all
       select c.id
       from Comments c
              inner join
            IDs i
              on
                 c.ParentID = i.id
    )
    insert into @deletions(ID)
    select ID from IDs

    -- Delete from FK referenced table
    DELETE FROM OtherTable
    WHERE CommentID in (select ID from @deletions)

    --This delete comes last
    DELETE FROM Comments
    WHERE id in (select ID from @deletions);

Self referencing foreign-key constraints and delete

Unlike Andomar, I'd be happy using a trigger - but I wouldn't remove the constraint checking. If you implement it as an instead of trigger, you can reset the other rows to null before performing the actual delete:

CREATE TRIGGER T_tabData_D
on tabData
instead of delete
as
    set nocount on
    update tabData set fiData = null where fiData in (select idData from deleted)
    delete from tabData where idData in (select idData from deleted)

It's short, it's succinct, it wouldn't be necessary if SQL Server could handle foreign key cascades to the same table (in other RDBMS', you may be able to just specify ON DELETE SET NULL for the foreign key constraint, YMMV).

---

Related Topics