問題描述

我需要一個表格來保存這樣的層次樹數(shù)據(jù)(即大陸、國家、城市)

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 并希望該表級聯(lián)刪除所有法國城市.但是當(dāng)我像這樣創(chuàng)建表時

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'位置"可能會導(dǎo)致循環(huán)或多個級聯(lián)路徑.指定開啟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

--此觸發(fā)器將使用遞歸 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

--清理(仔細(xì)處理真實數(shù)據(jù)！)

--Clean-Up (Carefull with real data!)

if exists(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME='locations')
---DROP TABLE locations;

這篇關(guān)于SQL Server:帶有級聯(lián)刪除外鍵的表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！