sql-server - 如何在检查约束中使用递归 CTE？

Question

我正在尝试check constraint在表上创建一个，以便 ParentID 永远不是当前记录的后代

例如，我有一个表类别，具有以下字段 ID、名称、ParentID

我有以下CTE

WITH Children AS (SELECT ID AS AncestorID, ID, ParentID AS NextAncestorID FROM Categories UNION ALL SELECT Categories.ID, Children.ID, Categories.ParentID FROM Categories JOIN Children ON Categories.ID = Children.NextAncestorID) SELECT ID FROM Children where AncestorID =99

这里的结果是正确的，但是当我尝试将它作为约束添加到表中时，如下所示：

ALTER TABLE dbo.Categories ADD CONSTRAINT CK_Categories CHECK (ParentID NOT IN(WITH Children AS (SELECT ID AS AncestorID, ID, ParentID AS NextAncestorID FROM Categories UNION ALL SELECT Categories.ID, Children.ID, Categories.ParentID FROM Categories JOIN Children ON Categories.ID = Children.NextAncestorID) SELECT ID FROM Children where AncestorID =ID))

我收到以下错误：

关键字“with”附近的语法不正确。如果此语句是公用表表达式、xmlnamespaces 子句或更改跟踪上下文子句，则前面的语句必须以分号结束。

在 ,之前添加分号WITH没有帮助。

这样做的正确方法是什么？

谢谢！

标签： sql-servercommon-table-expressioncheck-constraints

Answer 1

根据关于列约束的 SQL Server 文档：

查看

是通过限制可以输入到一列或多列中的可能值来强制域完整性的约束。

逻辑表达式

是 CHECK 约束中使用的逻辑表达式，返回 TRUE 或 FALSE。与 CHECK 约束一起使用的logical_expression 不能引用另一个表，但可以为同一行引用同一个表中的其他列。表达式不能引用别名数据类型。

（以上引用自文档的 SQL Server 2017 版本，但一般原则也适用于所有以前的版本，您没有说明您正在使用的版本。）

这里的重要部分是“不能引用另一个表，但可以为同一行引用同一个表中的其他列”（强调添加）。CTE 将被视为另一个表。

因此，您不能有一个复杂的查询，例如用于 CHECK 约束的 CTE。就像 Saman 建议的那样，如果您想检查其他行中的现有数据，并且它必须在 DB 层中，您可以将其作为触发器来执行。

然而，触发器有其自身的缺点（例如，可发现性问题、那些不知道触发器存在的人意想不到的行为）。

正如 Sami 在他们的评论中所建议的那样，另一个选项是 UDF，但这不是没有它自己的问题，根据SQL Server 2008 中关于这种方法的这个问题的答案，可能具有性能和稳定性。它可能仍然适用于以后的版本。

如果可能的话，我会说通常最好将该逻辑移动到应用程序层。我从您的评论中看到您已经进行了“客户端”验证。如果在该客户端和数据库服务器之间（例如在 Web 应用程序中）有一个应用程序服务器，我建议将额外的验证放在那里（在应用程序服务器中）而不是在数据库中。