Check Constraint - Subqueries are not allowed in this context

Question

I tried 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

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

@AaronBertrand I put the logic inside a UDF and return the value for that UDF. Then I compared against it inside the constraint and it didn't complain. Would trigger be better? — tugberk, Mar 15 '12 at 15:09
@AaronBertrand It didn't complain when I created but it acts unexpectedly when I try to add value into table. — tugberk, Mar 15 '12 at 15:23
A trigger can be a bit easier to manage. In addition an `INSTEAD OF` trigger can prevent other constraints from being validated when the row is inserted (since you can just bail out of the insert altogether if your check doesn't validate). — Aaron Bertrand, Mar 15 '12 at 15:38
You have to be careful, whatever route you go down, that you cover interesting edge cases. E.g. what happens if a single insert statement contains two rows that have an undesirable overlap - you need to be considering the *current* rows in the table, and *all of* the new rows to be added as a whole. — Damien_The_Unbeliever, Mar 15 '12 at 15:42
Are `dbo.PropertySeasonDiscount` and `dbo.PropertySeasonDiscounts` the same table? (Is it plural or not, or are they actually two different tables?) — Aaron Bertrand, Mar 15 '12 at 15:51

score 11 · Accepted Answer · answered Mar 15 '12 at 16:10

SQL Server does not currently support subqueries for CHECK CONSTRAINTs.

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.

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

score 3 · Answer 2 · answered Mar 16 '12 at 10:16

As others have mentioned already, this type of Check constraints is not yet implemented in SQL-Server. Besides triggers, you could also examine the possibility of changing the table's design.

A possible alternative includes storing the previous interval end-date in every row. See Storing intervals of time with no overlaps for details. The enforcing constraints are simple but there are complications on the way you'll have to deal with Inserts/Deletes/Updates on the table.

Another possibility would be to store not one row (with the start and end-date) for a discount as you do now but a whole series of rows (one for every date of the discount interval). The enforcing constraints will be even simpler but you'll have a lot of rows instead of every one of your current table.

Check Constraint - Subqueries are not allowed in this context

2 Answers2

Linked