Constraint

  • Hi all,

    I have a table with 3 bit fields: A, B, C.

    I need to place a constraint so that when A, or B, or both are true, then C must be false.  And when C is true, both A and B must be false.

    How can I accomplish this?  I'd prefer to use constraints and not triggers if possible.

    Thanks,

    Paul

  • One option you have open to you is to create a user defined function and add that as part of a check contraint. Here's an example from BOL that may be a useful starting point

    CREATE TABLE CheckTbl (col1 int, col2 int);
    GO
    CREATE FUNCTION CheckFnctn()
    RETURNS int
    AS 
    BEGIN
       DECLARE @retval int
       SELECT @retval = COUNT(*) FROM CheckTbl
       RETURN @retval
    END;
    GO
    ALTER TABLE CheckTblADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );
    GO
    Just pass in columns A, B and C then return the boolean result of your calculation

    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • Yep, sounds good.  Thanks.

  • CREATE

    TABLE TestBits(

    thekey

    INT PRIMARY KEY,

    A

    BIT,

    B

    BIT,

    C

    BIT,

    CONSTRAINT

    ck_bits CHECK ((A | B ^ C) = 1))

    INSERT

    INTO TestBits VALUES (1, 1, 1, 1) -- fails

    INSERT

    INTO TestBits VALUES (2, 0, 1, 1) -- fails

    INSERT

    INTO TestBits VALUES (3, 1, 0, 1) -- fails

    INSERT

    INTO TestBits VALUES (4, 0, 0, 1) -- works

    INSERT

    INTO TestBits VALUES (5, 0, 0, 0) -- fails

    INSERT

    INTO TestBits VALUES (6, 1, 0, 0) -- works

    INSERT

    INTO TestBits VALUES (7, 0, 1, 0) -- works

    INSERT

    INTO TestBits VALUES (8, 1, 1, 0) -- works

  • Nice - bitwise operators. Elegant, fast and doesn't hide the calculation away.


    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

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

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