Check constraint on multiple columns

  • I have a case where if the Id field is a specific value, I don't want to allow null in another field, but if the Id value <> a specific value, null is ok.

    In the example below, inserting the first record should succeed, the second should succeed, and the 3rd should fail. Right now the 2nd two fail. I gotta be missing something easy, but I can't figure it out.

    Any ideas?

    USE tempdb

    GO

    IF OBJECT_ID('tempdb.dbo.CheckConstraintTest') IS NOT NULL

    DROP TABLE tempdb.dbo.CheckConstraintTest;

    CREATE TABLE CheckConstraintTest

    (

    CTId INT NOT NULL ,

    Goal INT NULL ,

    CONSTRAINT CK_CheckConstraintTest_Goal CHECK ( CTId = 1

    AND Goal IS NOT NULL )

    );

    INSERT INTO CheckConstraintTest

    ( CTId, Goal )

    VALUES ( 1, 0 );

    INSERT INTO CheckConstraintTest

    ( CTId, Goal )

    VALUES ( 2, NULL );

    INSERT INTO CheckConstraintTest

    ( CTId, Goal )

    VALUES ( 1, NULL );

  • The first INSERT succeeds because both "CTId = 1" is TRUE and "Goal IS NOT NULL" is TRUE.

    The second INSERT fails because "CTId = 1" is not TRUE.

    The third INSERT fails because "Goal IS NOT NULL" is not TRUE.

    Since you have "AND" in the test, both conditions must be TRUE for success.

  • Wrap your conditions in parenthesis and add an OR

    CREATE TABLE CheckConstraintTest

    (

    CTId INT NOT NULL ,

    Goal INT NULL ,

    CONSTRAINT CK_CheckConstraintTest_Goal CHECK (

    (CTId = 1 AND Goal IS NOT NULL )

    OR

    (CTId <> 1)

    )

    );

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Argh, I should have been able to figure that out.

    Thanks!

  • Jason Selburg (7/25/2014)


    Wrap your conditions in parenthesis and add an OR

    CREATE TABLE CheckConstraintTest

    (

    CTId INT NOT NULL ,

    Goal INT NULL ,

    CONSTRAINT CK_CheckConstraintTest_Goal CHECK (

    (CTId = 1 AND Goal IS NOT NULL )

    OR

    (CTId <> 1)

    )

    );

    That's logicall correct but a bit redundant, you could just use

    CREATE TABLE CheckConstraintTest

    (

    CTId INT NOT NULL ,

    Goal INT NULL ,

    CONSTRAINT CK_CheckConstraintTest_Goal CHECK (

    (Goal IS NOT NULL)

    OR

    (CTId <> 1)

    )

    );

    Tom

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply