问题描述
我需要一个表格来保存这样的层次树数据(即大陆、国家、城市)
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.
信息说
- 指定ON DELETE NO ACTION - 这正是我不想要的
- 指定ON UPDATE NO ACTION - 指定
- 修改其他 FOREIGN KEY 约束 - 我不明白这个
- 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
有人可以帮忙吗?
推荐答案
这是不可能的.您可以使用 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;