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?

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 — Toby, Aug 09 '13 at 12:16
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) — Toby, Aug 09 '13 at 12:18
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. — Damien_The_Unbeliever, Aug 09 '13 at 12:20
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. — alroc, Aug 09 '13 at 12:39
Just realised I can get it free from the MS DreamSpark programme - will add it to the list of things to be learned ;) Cheers — Toby, Aug 09 '13 at 12:42

score 11 · Accepted Answer · answered Aug 09 '13 at 12:17

11

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.

answered Aug 09 '13 at 12:17

PinnyM

35,165
3
73
81

Thanks - also thanks for showing sqfiddle, didnt know that existed, will be very handy :) – Toby Aug 09 '13 at 12:21
but will it check for both condition means viceversa too ?? – Dhaval Aug 09 '13 at 12:35
@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. – PinnyM Aug 09 '13 at 12:49
@PinnyM 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 – Toby Aug 09 '13 at 13:01

score 5 · Answer 2 · edited Aug 09 '13 at 12:33

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

score 2 · Answer 3 · answered Aug 09 '13 at 12:34

2

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))

answered Aug 09 '13 at 12:34

Dhaval

2,801
20
39

Wow, that is so far over my head! Could you explain why doing this would be better than the simple AND/OR constraint? – Toby Aug 09 '13 at 12:44
1

@Toby .. you are right ... I have just tried to explain this can be done – Dhaval Aug 12 '13 at 14:03

Can CHECK constraints act like if else?

3 Answers3