Bitwise operations for Check or Foreign Key Constraint

  • I am running into a problem getting either a check constraint / foreign key constraint / etc to validate based on some bitwise operations. This example is oversimplified so please don't ask "why would you do that?"

    Table1 has

    ID Value

    1 2

    2 4

    3 8

    4 16

    etc.

    Table2 has

    ID Name Flags

    1 Test1 2

    2 Test2 6

    3 Test3 7 -- Should error on input

    What I am trying to do is make sure that the only values permitted are valid (note, there could be a gap, so I cannot just test to see that the value is even, or 2^x, etc).

    In other words, using Table2.Flags & Table1.Value > 0

    I tried a check constraint, but it won't allow a column from another table.

    I cannot do a foreign key, because '6' won't exist (it is Flags 2 | 4).

    I tried a Function, but it won't allow SP_EXECUTESQL.

    CREATE FUNCTION CheckFlags (

    @LookupTableSYSNAME,

    @LookupColumn SYSNAME,

    @CompareValueBIGINT

    ) RETURNS BIT AS BEGIN

    DECLARE @IsSetBIT

    DECLARE @valueBIT

    DECLARE @pvalueBIT

    DECLARE @paramNVARCHAR(250)

    DECLARE @sqlNVARCHAR(4000)

    SET @sql = 'SELECT @value =

    CASE

    WHEN COUNT(@@CompareValue@@ & @@LookupTable@@.@@LookupColumn@@) > 0

    THEN 1

    ELSE 0

    END

    FROM @@LookupTable@@

    WHERE (@@LookupTable@@.@@LookupColumn@@ & @@CompareValue@@) <> 0'

    SET @param = '@value INT OUTPUT'

    SET @sql = REPLACE(@sql, '@@LookupTable@@',@LookupTable)

    SET @sql = REPLACE(@sql, '@@LookupColumn@@',@LookupColumn)

    SET @sql = REPLACE(@sql, '@@CompareValue@@',@CompareValue)

    EXECUTE SP_EXECUTESQL @sql, @param, @value = @pvalue OUTPUT

    SET @IsSet = @pvalue

    RETURN @IsSet

    END

    So, how do I ensure that when new data is entered, that there is a check to ensure that the flags used are only those available?

  • I did a little more testing, and came up with a simple solution using a trigger:

    CREATE TRIGGER [dbo].[BitTrigger]

    ON [dbo].[Table2]

    FOR INSERT, UPDATE

    AS BEGIN

    DECLARE @Result BIT

    DECLARE @Flags INT

    SELECT @Flags = Flags FROM inserted

    SELECT @Result = CASE WHEN SUM([Value]) & @Flags = @Flags THEN 1 ELSE 0 END

    FROM Table1

    IF @Result = 0 BEGIN

    RAISERROR ('This is not an acceptable combination of values.', 16, 1)

    ROLLBACK TRANSACTION

    END

    END

  • Does the following code give you any ideas on how to approach this problem?

    declare @BitTest int,

    @BitValue int;

    set @BitTest = 2 + 4 + 8 + 16;

    set @BitValue = 7;

    select case when (@BitTest & @BitValue) = @BitValue then 'true' else 'false' end;

    set @BitTest = 2 + 4 + 8 + 16;

    set @BitValue = 6;

    select case when (@BitTest & @BitValue) = @BitValue then 'true' else 'false' end;

  • Looks like you came up with the same idea as i was putting together some test code myself.

    Good work.

  • Of course, now I had to do the other table so that I could not accidentally delete a record that is being referenced.

    It would be nice if there were an actual way to do a foreign key or check constraint.

    CREATE TRIGGER [dbo].[TriggerTable1]

    ON [dbo].[Table1]

    FOR DELETE

    AS BEGIN

    DECLARE @Result INT

    DECLARE @Value INT

    SELECT @Value = [Value] FROM deleted

    SELECT @Result = SUM(CASE WHEN [Type] & @Value = @Value THEN 1 ELSE 0 END)

    FROM Table2

    IF @Result 0 BEGIN

    RAISERROR ('Cannot delete record as column [Value] (%d) is used in table Table2.', 16, 1, @Value)

    ROLLBACK TRANSACTION

    END

    END

    Especially since I may use this table a few times, and the logic using bitflags quite a bit.

  • Sounds like a challenge 🙂

    I can be done with FOREIGN KEYS as shown below. (I'm not saying it isn't without its own problems however).

    As a side-benefit, this approach also gives you statistics for each bit.

    USE tempdb

    GO

    --DROP TABLE dbo.Flags

    GO

    -- Table of valid flags

    CREATE TABLE dbo.Flags

    (

    positionINTNOT NULL PRIMARY KEY CLUSTERED CHECK (position BETWEEN -1 AND 15),

    bit_valueINTNOT NULL UNIQUE NONCLUSTERED,

    CHECK (bit_value = POWER(2, position) OR (position = -1 AND bit_value = 0))

    );

    GO

    -- Add 16 bits plus a placeholder to allow unset bits

    INSERTdbo.Flags (position, bit_value)

    SELECTTOP (16)

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS position,

    POWER(2, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) AS bit_value

    FROMmaster.sys.columns

    UNIONALL

    SELECT-1, 0 -- for unset bits

    GO

    CREATE TABLE dbo.Data

    (

    flagsINTNOT NULL,

    flag00AS(flags & 1) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    flag01AS(flags & 2) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    flag02AS(flags & 4) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    flag03AS(flags & 8) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    flag04AS(flags & 16) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    flag05AS(flags & 32) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    flag06AS(flags & 64) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    flag07AS(flags & 128) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    flag08AS(flags & 256) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    flag09AS(flags & 512) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    flag10AS(flags & 1024) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    flag11AS(flags & 2048) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    flag12AS(flags & 4096) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    flag13AS(flags & 8192) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    flag14AS(flags & 16384) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    flag15AS(flags & 32768) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

    )

    GO

    DELETE dbo.Flags WHERE bit_value = 64-- this flag is not allowed

    DELETE dbo.Flags WHERE bit_value = 16384-- this flag is not allowed

    GO

    INSERTdbo.Data VALUES (63)-- OK

    INSERTdbo.Data VALUES (65)-- ERROR (64 not allowed)

    INSERTdbo.Data VALUES (16383)-- OK

    INSERTdbo.Data VALUES (16386)-- ERROR (16384 not allowed)

    GO

    DROP TABLE dbo.Flags;

  • You could, of course, also populate a table with all the valid values for the flag combinations and directly foreign key on that.

  • If I understand the problem, you want to raise error if flags contains a bit not in table1.

    CREATE TRIGGER [dbo].[BitTrigger]

    ON [dbo].[Table2]

    FOR INSERT, UPDATE

    AS BEGIN

    DECLARE @AllFlags INT

    SELECT @AllFlags = SUM([Value]) FROM Table1;

    if exists(select 1 from inserted where Flags &~@AllFlags>0) begin

    RAISERROR ('This is not an acceptable combination of values.', 16, 1)

    ROLLBACK TRANSACTION

    END

    END

    "&~" is bitwise "and not". Oops, code tag does a strange thing to "~", so correct expression is select 1 from inserted where Flags &~@AllFlags>0

    Is Flags=0 acceptable?

    If not, you just modify it to:

    if exists(select 1 from inserted where Flags &~@AllFlags>0 or Flags=0) begin

  • I was trying to figure out if there was a way to do it without every possible combination.

    For example, suppose the values available are 2, 4 and 8 (just to keep it simple).

    TableValues

    ID Value

    1 2

    2 4

    3 8

    INSERT INTO TableInput (Flags) VALUES (6)

    What I am wondering if there is a way (which I might be answering my own question), to do a check or foreign key constraint on

    SELECT SUM(Value) FROM TableValues

    & inserted.Flags

    etc.

    Or something similar...

  • This looks like a pretty good solution. I will play with it.

    CREATE TABLE dbo.Flags

    (

    position INT NOT NULL PRIMARY KEY CLUSTERED CHECK (position BETWEEN -1 AND 15),

    bit_value INT NOT NULL UNIQUE NONCLUSTERED,

    CHECK (bit_value = POWER(2, position) OR (position = -1 AND bit_value = 0))

    );

    GO

    -- Add 16 bits plus a placeholder to allow unset bits

    INSERT dbo.Flags (position, bit_value)

    SELECT TOP (16)

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS position,

    POWER(2, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) AS bit_value

    FROM master.sys.columns

    UNION ALL

    SELECT -1, 0 -- for unset bits

    GO

  • adammenkes (6/15/2009)


    This looks like a pretty good solution. I will play with it.

    I posted that just to prove it could be done with FOREIGN KEYs - it's not a recommended solution by any means. The modification I posted just after that might be workable though.

    Paul

Viewing 11 posts - 1 through 10 (of 10 total)

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