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?

score 9 · Accepted Answer · answered Feb 09 '09 at 15:42

9

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.

answered Feb 09 '09 at 15:42

Timbo

4,505
2
26
29

PostgreSQL supports it. Now if another entry that is not to be deleted references a subentry that is to be deleted, it will issue an error, and the delete operation will be rolled back. Otherwise, it will work perfectly. The real issue is, whether your parent-child relationship is a non-cyclic directed graph or not. Because most self-references implement hierarchies, and hierarchies should be directed non-cyclic graphs, PostgreSQL's course of action is bound to be more sane in most cases. In all the other cases, you can still implement that functionality manually, so nothing is lost. – Stefan Steiger Sep 15 '15 at 09:26

score 4 · Answer 2 · edited May 23 '17 at 12:00

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;

I have a table that references itself up to three levels deep, did extensive testing with this and it works perfectly. Thank you so much! — vaindil, Dec 22 '16 at 23:45

score 0 · Answer 3 · edited Nov 07 '12 at 11:41

0

CREATE TRIGGER MyTable_OnDelete ON MyTable
INSTEAD OF DELETE
AS 
BEGIN

  SET NOCOUNT ON;

  DELETE FROM mt
  FROM   deleted AS D
  JOIN   MyTable AS mt
  ON     d.Id = mt.ParentId

  DELETE FROM mt
  FROM   deleted AS D
  JOIN   MyTable AS mt
  ON     d.Id = mt.Id

END

edited Nov 07 '12 at 11:41

Florent

12,310
10
49
58

answered Nov 07 '12 at 11:24

Mikael Sälle

1

1

Sorry but In one case if `MyTable` is in relationship with another table (having `ON DELETE CASCADE...`) you cannot define an `INSTEAD OF DELETE` trigger... – Bellash Sep 19 '14 at 09:03

Self-referencing constraint in MS SQL

3 Answers3

Linked