I created this CHECK Constraint to guarantee at least 2 of the 4 fields are equivalent in an entry. Why is it not working?

user_15501571 0
Viewed 39

I am attempting to create a constraint to enforce each row to have at least two of the four fields equivalent to each other.

I came up with

    CHECK(
    (CASE WHEN (CAMPO1=CAMPO2)
    OR (CAMPO1=CAMPO3)
    OR (CAMPO1=CAMPO4) 
    OR (CAMPO2=CAMPO3)
    OR (CAMPO2=CAMPO4)
    OR (CAMPO3 = CAMPO4)
    THEN 1 END) = 1 );

However, to my surprise, the following INSERT statement went through.

INSERT INTO Ejercicio2A_2(
    CAMPO1,
    CAMPO2,
    CAMPO3,
    CAMPO4)
VALUES('A','B','C','D')

None of the values are equal to the other so I needed the constraint to reject the INSERT statement. Why did it not?

sql-server sql
2 Answers

No case expression is necessary:

(CAMPO1 = CAMPO2 OR CAMPO1 = CAMPO3 OR CAMPO1 = CAMPO4 OR
 CAMPO2 = CAMPO3 OR CAMPO2 = CAMPO4 OR
 CAMPO3 = CAMPO4
)

Or more concisely as:

(CAMPO1 IN (CAMPO2, CAMPO3, CAMPO4) OR
 CAMPO2 IN (CAMPO3, CAMPO4) OR
 CAMPO3 IN (CAMPO4)
)
user_11440350

Why did it not?

SQL uses three valued logic. Boolean expressions can evaluate to true, false, or unknown.

You would need an ELSE 0

Otherwise the CASE expression evaluates to NULL.

NULL = 1 is unknown.

Check constraints only reject false values and allow true and unknown.

But, as @Gordon points out there would be no value in doing

CHECK (CASE WHEN  <CONDITION> THEN 1 ELSE 0 END) = 1 );

anyway - and this should just be written

CHECK (<CONDITION>);
user_732260
Related