Post reply

Bitwise operations for Check or Foreign Key Constraint

  • June 1, 2009 at 10:07 pm

    #221847

    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?

  • June 1, 2009 at 10:54 pm

    #1003161

    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

  • June 1, 2009 at 11:07 pm

    #1003169

    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;

  • June 1, 2009 at 11:08 pm

    #1003170

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

    Good work.

  • June 1, 2009 at 11:37 pm

    #1003176

    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.

  • June 13, 2009 at 7:05 am

    #1009067

    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;

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • June 13, 2009 at 7:13 am

    #1009069

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

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • June 15, 2009 at 2:48 am

    #1009508

    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

  • June 15, 2009 at 2:38 pm

    #1009917

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

  • June 15, 2009 at 2:41 pm

    #1009920

    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

  • June 15, 2009 at 10:51 pm

    #1010022

    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

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy