检查约束 - 在此上下文中不允许子查询 [英] Check Constraint - Subqueries are not allowed in this context

问题描述

我累了添加检查约束，我到目前为止只失败了。

I tired to add a Check Constraint and I have only failed so far. What would be the way to get around this:

Msg 1046，Level 15，State 1，Line 6

Msg 1046, Level 15, State 1, Line 6

在此上下文中不允许使用子查询。只允许标量表达式
。

Subqueries are not allowed in this context. Only scalar expressions are allowed.

这是代码：

ALTER TABLE dbo.PropertySeasonDiscount ADD CONSTRAINT
[CC_PropertySeasonDiscount_MadeFrom_MadeTo]
CHECK (
    (SELECT COUNT(PropertySeasonDiscountId) FROM dbo.PropertySeasonDiscounts apsdeb 
        WHERE 
            (apsdeb.PropertySeasonId = PropertySeasonId) AND
            (
                (apsdeb.ValidForReservationsMadeTo >= ValidForReservationsMadeFrom AND ValidForReservationsMadeFrom >= apsdeb.ValidForReservationsMadeFrom) OR
                (apsdeb.ValidForReservationsMadeFrom <= ValidForReservationsMadeTo AND ValidForReservationsMadeTo <= apsdeb.ValidForReservationsMadeTo)
            )
    ) = 0
);

推荐答案

SQL Server目前不支持CHECK CONSTRAINT的子查询。

SQL Server does not currently support subqueries for CHECK CONSTRAINTs.

正如您所发现的，您可能会遇到 CHECK的问题

As you have discovered, there can be trouble with CHECK constraints involving UDFs when attempting to circumvent the subquery limitation.

替代约束实施策略是触发的程序和嵌入程序。

The alternative constraint implementation strategies are triggered procedural and embedded procedural. The former is preferred because, in common with declarative constraints, they cannot be circumvented.

实现一个经过优化并处理并发问题的触发程序策略是非常重要的，但是仍然可行。我强烈推荐这本书数据库专业人员应用数学作者：Lex de Haan，Toon Koppelaars ，第11章可以轻松地移植到SQL Server）。

Implementing a triggered procedural strategy that is well optimized and handles concurrency issues is non-trivial but still doable. I highly recommend the book Applied Mathematics for Database Professionals By Lex de Haan, Toon Koppelaars, chapter 11 (the code examples are Oracle but can be easily ported to SQL Server).

这篇关于检查约束 - 在此上下文中不允许子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！