Check Constraints and Case Statement

Question

I need help with this check constraint, I get the following error message: "Msg 102, Level 15, State 1, Line 14 Incorrect syntax near '='."

Or maybe the question I should ask is if this is possible using a check constraint

What I am trying to achieve is: If InformationRestricted is True, InformationNotRestricted cannot be true and InformationRestrictedFromLevel1, InformationRestrictedFromLevel2, InformationRestrictedFromLevel3, InformationRestrictedFromLevel4, InformationRestrictedFromLevel5 cannot be true

I am not trying to assign values to the columns, just trying to ensure the values of the columns = 0 (i.e. false) if InformationRestricted is True

Here is the script:

    CREATE TABLE EmployeeData
    (FirstName varchar(50),
    Last Name varchar(50),
    Age int,
    Address varchar(100),
    InformationRestricted bit,
    InformationNotRestricted bit,
    InformationRestrictedFromLevel1 bit,
    InformationRestrictedFromLevel2 bit
    InformationRestrictedFromLevel3 bit
    InformationRestrictedFromLevel4 bit
    InformationRestrictedFromLevel5 bit);

    ALTER TABLE EmployeeData ADD CONSTRAINT ck_EmployeeData
    CHECK (CASE WHEN InformationRestricted = 1 THEN InformationNotRestricted = 0         --InformationRestricted is true, InformationNotRestricted is false
    AND( InformationRestrictedFromLevel1 = 0 --is false
    OR InformationRestrictedFromLevel2 = 0 --is false
    OR InformationRestrictedFromLevel3 = 0 --is false
    OR InformationRestrictedFromLevel4 = 0 --is false
    OR InformationRestrictedFromLevel5 = 0)); --is false

Answer 1

A CASE expression is something that returns a value of a particular data type (the type to be determined by the various datatypes of each THEN clause).

SQL Server doesn't have a boolean data type, so you can't return the result of a comparison operation.

Try adding additional comparisons into WHEN clauses, and having the THENs return either 1 or 0, if you want to allow or disallow the outcome (respectively). Then compare the overall result to 1.

I can't parse out the sense of your condition entirely, but something like:

CHECK(CASE WHEN InformationRestricted = 1 THEN
     CASE WHEN InformationNotRestricted = 0 AND
        (InformationRestrictedFromLevel1 = 0 --is false
        OR InformationRestrictedFromLevel2 = 0 --is false
        OR InformationRestrictedFromLevel3 = 0 --is false
        OR InformationRestrictedFromLevel4 = 0 --is false
        OR InformationRestrictedFromLevel5 = 0)
         THEN 1
         ELSE 0
     END
--Other conditions?
END = 1)

---

My confusion is I'd have though you'd want to check that one and only one of the InformationRestrictedFromXXX columns would be one. In fact, from the general description, (without knowing more about your problem domain), I'd have probably just created a column InformationRestrictionLevel, of type int, with 0 meaning unrestricted, and higher values indicating the level it's restricted from.

Answer 2

Looks like you're not closing the case with end. The basic format of a check constraint using case is:

check(case when <condition> then 1 else 0 end = 1)

If you nest multiple cases, be sure to match the number of cases with the number of ends:

check
(
    1 =
    case 
    when <condition> then 
        case 
        when <condition> then 1 
        else 0 
        end 
    else 0 
    end
)

Formatting all elements of the same case with the same indentation can be a big help.