代码之家 › 专栏 › 技术社区 › Yisroel M. Olewski

如何在check约束中使用递归cte?

check-constraints common-table-expression sql-server

0

Yisroel M. Olewski · 技术社区 · 5 年前

我想创造一个 check constraint 使parentID永远不是当前记录的后代

例如,我有一个表类别,具有以下字段id、name、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 ,没有帮助。

正确的方法是什么?

谢谢!

0 回复 | 直到 5 年前

1

Kanmuri 5 年前

根据上的SQL Server文档 column constraints :

检查

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

逻辑表达式

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

(上述内容引用自SQL Server 2017版本的文档,但一般原则也适用于所有以前的版本,并且您没有说明正在使用的版本。)

这里的重要部分是“不能引用另一个表,但可以引用 同一张桌子 对于 同一行 “(强调的是)。一张CTE就等于另一张桌子。

因此,不能有像用于check约束的cte这样的复杂查询。就像saman建议的那样,如果要检查其他行中的现有数据,那么必须在数据库层,你可以作为一个触发器。

然而,触发器有其自身的缺点(例如,可发现性问题,那些不知道触发器存在的人无法预料的行为)。

正如sami在他们的评论中所建议的,另一个选择是udf,但这不是它自己的问题,根据对 this question about this approach in SQL Server 2008 . 它可能仍然适用于以后的版本。

如果可能的话,我会说通常最好将逻辑转移到应用程序层。我从你的评论中看到你已经得到了“客户端”的验证。如果在客户端和数据库服务器之间有一个应用服务器(例如在web应用中),我建议将额外的验证放在那里(在应用服务器中),而不是放在数据库中。