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