Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths

Question

I have this sql script to create database in mssql:

CREATE TABLE ENTITY (
  ID             bigint IDENTITY(1,1)             NOT NULL,
  NAME           nvarchar(255)                    NOT NULL,
  PARENT_ID      bigint                           NULL
)
go

ALTER TABLE ENTITY
ADD CONSTRAINT PK_ENTITY PRIMARY KEY CLUSTERED (ID)
WITH FILLFACTOR = 80
go

ALTER TABLE ENTITY
ADD CONSTRAINT FK_ENTITY FOREIGN KEY (PARENT_ID)
REFERENCES ENTITY (ID) ON DELETE CASCADE ON UPDATE CASCADE
go

By design i'd like to create parent->child relations in single table So, just some pseudocode examples:

id |  name |  parent_id
1    Mother     null
2    Steve      1
3    Jack       1

I'd like to remove Mother with all DELETE Cascade childs if needed But after i install this script i receive:

Introducing FOREIGN KEY constraint 'FK_ENTITY' on table 'ENTITY' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Is any solution how i can implement such single table hierarchy correctly?

You can't achieve what you want through foreign key cascades in SQL Server. The cycle it's identified is the simplest one possible - you want deletes in this table to cause further deletes within the same table. SQL Server doesn't support that. — Damien_The_Unbeliever
I will have recursion number no more than 3 steps, maybe i'm able to have some trigger or max_recursion parameter enabled? I believe microsoft has something for such a trivial task (like MySQL does) — ServerSideCat
@ServerSideCat Yes I think you can implement it manually using a trigger AFTER DELETE to delete all children of deleted row — user586399
@Kilanny, You'll need to write an INSTEAD OF DELETE trigger, not AFTER DELETE. The trigger has to delete all child rows before a parent row can be deleted. — Vladimir Baranov
@VladimirBaranov Yes you are right. I forgot that foreign key restriction. — user586399

Sepideh.Sp Sepideh.Sp · Accepted Answer · 2016-07-12T09:16:02

It seems sql server doesn't let Cascading DELETE with Recursive Foreign Keys, so you can write procedure for your delete action like below :

CREATE PROC DeleteChildWithParent(@ID BIGINT)
AS

;WITH ChildToDelete AS (
    SELECT  id, CAST(1 AS INT) AS ChildLevel
    FROM    ENTITY
    WHERE   id = @ID 
    UNION ALL
    SELECT  e.id, C.ChildLevel + 1
    FROM    ENTITY E
            JOIN ChildToDelete C ON e.PARENT_ID = C.ID AND E.PARENT_ID <> E.ID
)

SELECT  id, ROW_NUMBER() OVER (ORDER BY ChildLevel DESC) Ord
INTO    #ChildToDelete
FROM    ChildToDelete

DECLARE @count INT = 1, @max INT = @@ROWCOUNT;

WHILE @count <= @max
BEGIN
    DELETE ENTITY WHERE id = (SELECT id FROM #ChildToDelete WHERE Ord = @count);
    SET @count = @count + 1;
END;
GO

Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths

1 Answers