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

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

Can anyone help?

Answer 1

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;