Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Bitwise operations for Check or Foreign Key Constraint Expand / Collapse
Author
Message
Posted Monday, June 1, 2009 10:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 27, 2012 8:38 AM
Points: 10, Visits: 55
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?


Post #727121
Posted Monday, June 1, 2009 10:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 27, 2012 8:38 AM
Points: 10, Visits: 55
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


Post #727136
Posted Monday, June 1, 2009 11:07 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:49 PM
Points: 23,396, Visits: 32,222
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;




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)
Post #727146
Posted Monday, June 1, 2009 11:08 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:49 PM
Points: 23,396, Visits: 32,222
Looks like you came up with the same idea as i was putting together some test code myself.

Good work.



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)
Post #727147
Posted Monday, June 1, 2009 11:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 27, 2012 8:38 AM
Points: 10, Visits: 55
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.
Post #727153
Posted Saturday, June 13, 2009 7:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:01 AM
Points: 11,194, Visits: 11,165
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #734328
Posted Saturday, June 13, 2009 7:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:01 AM
Points: 11,194, Visits: 11,165
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #734330
Posted Monday, June 15, 2009 2:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:28 PM
Points: 1,218, Visits: 890
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
Post #734838
Posted Monday, June 15, 2009 2:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 27, 2012 8:38 AM
Points: 10, Visits: 55
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...


Post #735321
Posted Monday, June 15, 2009 2:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 27, 2012 8:38 AM
Points: 10, Visits: 55
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

Post #735324
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse