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?

Toth

No, my tables are on a live server with lots of other stuff that work uses, so I dont want to screw things up :O – Toth

Don't you have an equivalent development environment? – Ashburn

Nope, dont have a way to set one up here either, else thats how I would have started (also as I mentioned Im very new at SQL and wouldnt know where to start setting SQL server or DB up from scratch) – Toth

Yes, you can do it, but one thing that catches people out - if you create it, and later generate a script from the object, your bracketing and layout may look different. I'm not sure exactly how they're stored, but it's not as a string, and so when a string is re-generated, only the brackets that are strictly necessary are added in. – Selfmade

SQL Server Developer Edition costs $60 if you buy direct from MS (you can find it cheaper elsewhere - $50 on Newegg, even less on Amazon), and you can install it on your workstation. You need a non-production environment to learn and test things in. – Hysterotomy

Just realised I can get it free from the MS DreamSpark programme - will add it to the list of things to be learned ;) Cheers – Toth

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.

Fayfayal

Thanks - also thanks for showing sqfiddle, didnt know that existed, will be very handy :) – Toth

but will it check for both condition means viceversa too ?? – Airtoair

@Dhaval: You probably mean to point out that the logic should be simply ( (col1 NOT NULL AND col2 NOT NULL AND col3 NULL) OR (col3 NOT NULL AND col1 NULL AND col2 NULL) ). The problem with this is that it will always enforce that col1 and col2 have the same state. I think the OP purposely did not do this because col3 should be NULL if one of col1 or col2 are NOT NULL. – Fayfayal

@Fayfayal Correct, Col3 needs to be null if either of col1 or col2 are not null. But col1 and col2 should both be either null or not null together - but I can enforce this application side as otherwise I think the constraint regarding this and col3 becomes a bit more complex – Toth

Answer 2

Just be careful not to make mistake with 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

Explorer

Answer 3

I would say create a UDF like 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 following check statement in your table

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

Airtoair

Wow, that is so far over my head! Could you explain why doing this would be better than the simple AND/OR constraint? – Toth

@Toth .. you are right ... I have just tried to explain this can be done – Airtoair