Self-referencing constraint in MS SQL

Question

Is it true that MS SQL restrict self-referencing constraints with ON DELETE CASCADE option? I have a table with parent-child relation, PARENT_ID column is foreign key for ID. Creating it with ON DELETE CASCADE option causes error

"Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

I can't believe that I have to delete this hierarchy in recursive mode. Is there any issue except triggers?

Answer 1

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.

Answer 2

I just answered another question where this question was bound as duplicate. I think it's worth to place my answer here too:

This is not possible. You can solve this with an INSTEAD OF TRIGGER

create table locations 
(
    id int identity(1, 1),
    name varchar(255) not null,
    parent_id int,

    constraint pk__locations
        primary key clustered (id)

)
GO

INSERT INTO locations(name,parent_id)  VALUES
 ('world',null)
,('Europe',1)
,('Asia',1)
,('France',2)
,('Paris',4)
,('Lyon',4);
GO

--This trigger will use a recursive CTE to get all IDs following all ids you are deleting. These IDs are deleted.

CREATE TRIGGER dbo.DeleteCascadeLocations ON locations
INSTEAD OF DELETE 
AS
BEGIN
    WITH recCTE AS
    (
        SELECT id,parent_id
        FROM deleted

        UNION ALL

        SELECT nxt.id,nxt.parent_id
        FROM recCTE AS prv
        INNER JOIN locations AS nxt ON nxt.parent_id=prv.id
    )
    DELETE FROM locations WHERE id IN(SELECT id FROM recCTE);
END
GO

--Test it here, try with different IDs. You can try WHERE id IN(4,3) also...

SELECT * FROM locations;

DELETE FROM locations WHERE id=4;

SELECT * FROM locations
GO

--Clean-Up (Carefull with real data!)

if exists(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME='locations')
---DROP TABLE locations;