Check Constarint

  • I have a table with 3 columns

    A int Null

    B int Null

    C int Not Null -- Cloumn 'C' can have only 2 values 150 & 151

    I need to create a Check Constraint with the conditions

    if value in Column 'C' is 150 then Column 'A' should have a value for sure (ie IS Not Null)

    if value in Column 'C' is 151 then Column 'B' should have a value for sure (ie IS Not Null)

    Can any one help me how to create this perticular check constraint, and if needed a function also.

  • Edit: one check constraint is enough...

    CHECK ((C = 150 AND A IS NOT NULL) OR (C = 151 AND B IS NOT NULL))

    Not syntax checked, but should get you on the right path at least.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • USE test

    CREATE TABLE testConstraint (

    A int Null,

    B int Null,

    C int Not Null)

    alter table testConstraint

    add constraint threeColumn CHECK ((C = 150 AND A IS NOT NULL) OR (C = 151 AND B IS NOT NULL))

    --fail

    INSERT INTO testConstraint (A, B, C)

    VALUES (NULL, NULL, 150)

    --fail

    INSERT INTO testConstraint (A, B, C)

    VALUES (NULL, NULL, 151)

    --success

    INSERT INTO testConstraint (A, B, C)

    VALUES (21, NULL, 150)

    --success

    INSERT INTO testConstraint (A, B, C)

    VALUES (NULL, 200, 151)

    --success

    INSERT INTO testConstraint (A, B, C)

    VALUES (500, 600, 150)

    --success

    INSERT INTO testConstraint (A, B, C)

    VALUES (7000, 9000, 151)

    --fail

    INSERT INTO testConstraint (A, B, C)

    VALUES (500, 600, 156)

    --fail

    INSERT INTO testConstraint (A, B, C)

    VALUES (NULL, NULL, 156)

    Tested... Gail, any reason NOT to do 1 constraint from your point of view?

    Jared
    CE - Microsoft

  • Something like this?

    USE tempdb

    GO

    CREATE TABLE dbo.three_columns

    (

    A INT NULL,

    B INT NULL,

    C INT NOT NULL

    );

    GO

    ALTER TABLE dbo.three_columns ADD CONSTRAINT [ck_dbo.three_columns.C] CHECK

    (

    (C = 150 AND A IS NOT NULL)

    OR

    (C = 151 AND B IS NOT NULL)

    );

    GO

    Now that we have that out of the way...what does B mean when C = 150? what does A mean when C = 151? I guess I am wondering if A and B can collapsed into one column.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thanks you very much,

    Is there any way

    if the Column 'C' is allowed to have other values than 150 & 151

    In That perticular senario both columns A & B can accept nulls.

    How could I change this check constraint.

  • CREATE TABLE dbo.three_columns

    (

    A INT NULL,

    B INT NULL,

    C INT NOT NULL

    );

    GO

    ALTER TABLE dbo.three_columns ADD CONSTRAINT [ck_dbo.three_columns.C] CHECK

    (

    (C < 150 OR C > 151) OR

    (C = 150 AND A IS NOT NULL)

    OR

    (C = 151 AND B IS NOT NULL)

    );

    GO

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

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