Can CHECK constraints act like if else?

Question

I have a table with 4 columns:

(ID (PK, int, NOT NULL), col1 (NULL), col2 (NULL), col3 (NULL))

I'd like to add a CHECK constraint (table-level I think?) so that:

if col1 OR col2 are NOT NULL then col3 must be NULL

and

if col3 is NOT NULL then col1 AND col2 must be NULL

i.e. col3 should be null if col1 and col2 are not null or vice-versa

I am very new to SQL and SQL server though and am not sure how to actually implement this or even if it can/should be implemented?

I think maybe:

CHECK ( (col1 NOT NULL OR col2 NOT NULL AND col3 NULL) OR 
        (col3 NOT NULL AND col1 NULL AND col2 NULL) )

But I am not sure if the brackets can be used to group the logic like this? If not, how can this best be implemented?

Answer 1

Absolutely, you can do this. See this sqlfiddle.

However, you need to make sure you bracket your logic properly. You should never mix ANDs and ORs in the same bracketing scope. So:

(col1 NOT NULL OR col2 NOT NULL AND col3 NULL)

Needs to become:

((col1 NOT NULL OR col2 NOT NULL) AND col3 NULL)

Or:

(col1 NOT NULL OR (col2 NOT NULL AND col3 NULL))

Depending on your intent.