MS SQL Server Express 2005 中的级联树删除

发布于 2024-09-12 16:05:50 字数 1102 浏览 8 评论 0原文

一张表的名称为“Stages”，每个 Stage 可以有 0 到无穷多个子级。在“Stages”表中有一列名为“Parent”。此列是同一表“Stages”的外键。

我如何对这棵树进行级联删除？我想在删除此表中的任何行时，自动删除其所有子项及其子项的子项...

通过以下查询，

GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Stage_Stage]') AND parent_object_id = OBJECT_ID(N'[dbo].[Stage]'))
ALTER TABLE [dbo].[Stage]  WITH CHECK ADD  CONSTRAINT [FK_Stage_Stage] FOREIGN KEY([parent])
REFERENCES [dbo].[Stage] ([id]) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Stage] CHECK CONSTRAINT [FK_Stage_Stage]
GO

我收到此错误

Msg 1785, Level 16, State 0, Line 2
Introducing FOREIGN KEY constraint 'FK_Stage_Stage' on table 'Stage' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_Stage_Stage' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.

原文

One table has the name "Stages", every Stage can have 0 to infinity children. In the table "Stages" there is a column named "Parent". This column is foreign a key for the same table "Stages".

How would I make a cascading delete for this tree? I want to when deleting any row in this table, automatically delete all their children and their children's children...

With the following query

GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Stage_Stage]') AND parent_object_id = OBJECT_ID(N'[dbo].[Stage]'))
ALTER TABLE [dbo].[Stage]  WITH CHECK ADD  CONSTRAINT [FK_Stage_Stage] FOREIGN KEY([parent])
REFERENCES [dbo].[Stage] ([id]) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Stage] CHECK CONSTRAINT [FK_Stage_Stage]
GO

I get this error

Msg 1785, Level 16, State 0, Line 2
Introducing FOREIGN KEY constraint 'FK_Stage_Stage' on table 'Stage' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_Stage_Stage' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

可爱暴击 2024-09-19 16:05:50

为所有“子”表添加带有 ON DELETE CASCADE 选项的外键。

ALTER TABLE SomeChildTable 
CONSTRAINT YOurConstraintName 
FOREIGN KEY (YourParentId)
REFERENCES YourParentTable(ParentTableId) ON DELETE CASCADE;

对于新表：

CREATE TABLE ttt
(
  ...
  CONSTRAINT YOurConstraintName 
  FOREIGN KEY (YourParentId)
  REFERENCES YourParentTable(ParentTableId) ON DELETE CASCADE
)

Add foreigns key with ON DELETE CASCADE option for all "child" tables.

ALTER TABLE SomeChildTable 
CONSTRAINT YOurConstraintName 
FOREIGN KEY (YourParentId)
REFERENCES YourParentTable(ParentTableId) ON DELETE CASCADE;

for new tables:

CREATE TABLE ttt
(
  ...
  CONSTRAINT YOurConstraintName 
  FOREIGN KEY (YourParentId)
  REFERENCES YourParentTable(ParentTableId) ON DELETE CASCADE
)

回复收藏 0 原文

~没有更多了~