SQL Server:带有级联删除外键的表 [英] SQL Server : table with foreign key to itself with cascade delete

问题描述

我需要一个表格来保存这样的层次树数据(即大陆、国家、城市)

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

我想删除 France 并希望该表级联删除所有法国城市.但是当我像这样创建表时

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 
)

我有一个错误

在表上引入 FOREIGN KEY 约束 'fk__locations'位置"可能会导致循环或多个级联路径.指定开启DELETE NO ACTION 或 ON UPDATE NO ACTION，或修改其他 FOREIGN KEY约束.

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.

信息说

1. 指定ON DELETE NO ACTION - 这正是我不想要的
2. 指定ON UPDATE NO ACTION - 指定
3. 修改其他 FOREIGN KEY 约束 - 我不明白这个

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

有人可以帮忙吗?

推荐答案

这是不可能的.您可以使用 INSTEAD OF TRIGGER

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

--此触发器将使用递归 CTE 来获取您要删除的所有 ID 之后的所有 ID.这些 ID 被删除.

--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

--在这里测试，尝试使用不同的 ID.你也可以试试 WHERE id IN(4,3)...

--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;

这篇关于SQL Server:带有级联删除外键的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！