MS SQL 中的自引用约束 [英] Self-referencing constraint in MS SQL

问题描述

MS SQL 是否使用 ON DELETE CASCADE 选项限制自引用约束?我有一个具有父子关系的表，PARENT_ID 列是 ID 的外键.使用 ON DELETE CASCADE 选项创建它会导致错误

Is it true that MS SQL restrict self-referencing constraints with ON DELETE CASCADE option? I have a table with parent-child relation, PARENT_ID column is foreign key for ID. Creating it with ON DELETE CASCADE option causes error

"引入 FOREIGN KEY 约束可能导致循环或多重级联路径.指定 ON DELETE NO ACTION 或ON UPDATE NO ACTION，或修改其他外键约束."

"Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

我不敢相信我必须以递归模式删除这个层次结构.除了触发器还有什么问题吗?

I can't believe that I have to delete this hierarchy in recursive mode. Is there any issue except triggers?

推荐答案

存在无法在具有自引用约束的表上设置 ON DELETE CASCADE 的情况.存在循环逻辑问题的可能性，因此不允许.

It is the case that you cannot set up ON DELETE CASCADE on a table with self-referencing constraints. There is a potential of cyclical logic problems, hence it won't allow it.

有一篇很好的文章这里 - 不过它适用于 SQL 的第 8 版而不是第 9 版 - 尽管适用相同的规则.

There's a good article here - though it's for version 8 rather than 9 of SQL - though the same rules apply.

这篇关于MS SQL 中的自引用约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！