June 1, 2009 at 10:07 pm
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
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
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
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
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
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;
June 13, 2009 at 7:13 am
You could, of course, also populate a table with all the valid values for the flag combinations and directly foreign key on that.
June 15, 2009 at 2:48 am
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
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
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
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