Can CHECK restrictions act as if they were different?

Question

I have a table with 4 columns (ID (PK, int, NOT NULL), col1 (NULL), col2 (NULL), col3 (NULL))

I like to add a CHECK constraint (at the 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 must be null if col1 and col2 are not zero or vice versa

I am very new to SQL and SQL Server, although I’m not sure how to really 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'm not sure if parentheses can be used to group logic as follows?

If not, how is this best implemented?

Answer 1

Absolutely, you can do it. See sqlfiddle .

However, you need to make sure that you are binding your logic correctly. You should never mix AND and OR in the same bracketing area. So:

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

Need 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 intentions.

Answer 2

Just be careful not to make a mistake in brackets.

 CREATE TABLE Test1 (col1 INT, col2 INT, col3 INT); ALTER TABLE Test1 ADD CONSTRAINT CHK1 CHECK (((col1 IS NOT NULL OR col2 IS NOT NULL) AND col3 IS NULL) OR ((col1 IS NULL AND col2 IS NULL) AND col3 IS NOT NULL)) INSERT INTO Test1 VALUES (1,1,1); --fail INSERT INTO Test1 VALUES (1,1,NULL); --good INSERT INTO Test1 VALUES (1,NULL,NULL); --good INSERT INTO Test1 VALUES (1,NULL,1); --fail INSERT INTO Test1 VALUES (NULL,NULL,1); --good 

Answer 3

I would say create UDF as below

 create FUNCTION dbo.fn_check_val (@col1 int , @col2 int , @col3 int) RETURNS bit AS BEGIN declare @toRet bit IF(@col1 is Not null OR @col2 is NOT NULL) Begin if(@col3 is null) Begin Set @toRet = 1 End Else Begin Set @toRet = 0 End End Else if(@col3 is not null) Begin Set @toRet = 1 End Else Begin Set @toRet = 0 End return @toRet END 

and then add the following validation statement to the table

 ([dbo].[fn_check_val]([col1],[col2],[col3])=(1))