SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bitwise operations for Check or Foreign Key Constraint


Bitwise operations for Check or Foreign Key Constraint

Author
Message
adammenkes
adammenkes
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 56
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 (
@LookupTable SYSNAME,
@LookupColumn SYSNAME,
@CompareValue BIGINT
) RETURNS BIT AS BEGIN

DECLARE @IsSet BIT
DECLARE @value BIT
DECLARE @pvalue BIT
DECLARE @param NVARCHAR(250)
DECLARE @sql NVARCHAR(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?
adammenkes
adammenkes
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 56
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


Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96943 Visits: 38988
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;



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96943 Visits: 38988
Looks like you came up with the same idea as i was putting together some test code myself.

Good work.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
adammenkes
adammenkes
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 56
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.
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36288 Visits: 11361
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
(
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
CREATE TABLE dbo.Data
(
flags INT NOT NULL,
flag00 AS (flags & 1) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),
flag01 AS (flags & 2) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),
flag02 AS (flags & 4) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),
flag03 AS (flags & 8) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),
flag04 AS (flags & 16) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),
flag05 AS (flags & 32) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),
flag06 AS (flags & 64) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),
flag07 AS (flags & 128) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),
flag08 AS (flags & 256) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),
flag09 AS (flags & 512) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),
flag10 AS (flags & 1024) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),
flag11 AS (flags & 2048) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),
flag12 AS (flags & 4096) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),
flag13 AS (flags & 8192) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),
flag14 AS (flags & 16384) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),
flag15 AS (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
INSERT dbo.Data VALUES (63) -- OK
INSERT dbo.Data VALUES (65) -- ERROR (64 not allowed)
INSERT dbo.Data VALUES (16383) -- OK
INSERT dbo.Data VALUES (16386) -- ERROR (16384 not allowed)
GO
DROP TABLE dbo.Flags;





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36288 Visits: 11361
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Robert-378556
Robert-378556
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2374 Visits: 1010
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
adammenkes
adammenkes
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 56
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...
adammenkes
adammenkes
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 56
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search