Check restriction. Subqueries are not allowed in this context.

Question

I tried to add a Verification constraint, and so far I have failed. What will be the way around this:

Message 1046, Level 15, State 1, Line 6

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

This is the code:

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
);

Answer 1

SQL Server currently supports subqueries for CHECK CONSTRAINTs .

As you have discovered, there may be a problem with CHECK constraints using UDF when trying to bypass a subquery constraint.

- . , , , .

, concurrency, , . , Toon Koppelaars, 11 ( - Oracle, SQL Server).

Answer 2

, SQL-Server. , .

. . . , , // .

( ) , , ( ). , .