SQL Server : table with foreign key to itself with cascade delete

Question

I need a table table to hold hierarchy tree data (i.e. continents, countries, cities) like this

id  name    parent
-------------------
1   world   null
2   Europe  1
3   Asia    1
4   France  2
5   Paris   4
6   Lyon    4

I want to delete France and would expect the table to cascade delete all French cities. But when I create the table like this

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

    constraint pk__locations
        primary key clustered (id),

    constraint fk__locations
        foreign key (parent_id) 
            references locations (id)
                on delete cascade
                on update no action 
)

I got an error

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

Info says

to specify ON DELETE NO ACTION - that is exactly what do not I want
to specify ON UPDATE NO ACTION - specified
modify other FOREIGN KEY constraint - I don't understand this one

Can anyone help?

Possibly a duplicate: http://stackoverflow.com/questions/528529/self-referencing-constraint-in-ms-sql I think you need a trigger. — vercelli, Aug 16 '16 at 14:19

Shnugo · Accepted Answer · 2016-08-16T20:44:46.013

6

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;

edited Aug 16 '16 at 20:44

answered Aug 16 '16 at 14:30

Shnugo

66,100
9
53
114

the `@delid` is not used anywhere ,also is it possible this will be a recursive trigger? – Biju jose Aug 16 '16 at 19:15
@Bijujose, thx, the `@delid` is a forgotten residue from tests... What would be the advantage of a recursive trigger? The recursive CTE will fetch the whole branche from a given ID down to a depth of 100 (by default). All dependant rows will be deleted in one single statement. This should be the *maximum best optimal solution ;-)* – Shnugo Aug 16 '16 at 20:48
No not the advantage , I was thinking since we are deleting from the same table weather the trigger will fire indefinetly? – Biju jose Aug 17 '16 at 01:11
@Bijujose No problem here... The `INSTEAD OF TRIGGER` is a *one-time-action*, which takes place *instead of the normal deletion process*. – Shnugo Aug 17 '16 at 07:29
hey @Shnugo you hit two questions with one answer :) Sorry to all for a duplicate to this one [Self-referencing constraint in MS SQL](http://stackoverflow.com/questions/528529/self-referencing-constraint-in-ms-sql) – user3365784 Aug 17 '16 at 09:10

SQL Server : table with foreign key to itself with cascade delete

1 Answers1

Linked