October 30, 2006 at 3:57 pm
Hi all,
I have a table with 3 bit fields: A, B, C.
I need to place a constraint so that when A, or B, or both are true, then C must be false. And when C is true, both A and B must be false.
How can I accomplish this? I'd prefer to use constraints and not triggers if possible.
Thanks,
Paul
October 30, 2006 at 4:16 pm
One option you have open to you is to create a user defined function and add that as part of a check contraint. Here's an example from BOL that may be a useful starting point
CREATE TABLE CheckTbl (col1 int, col2 int);
GO
CREATE FUNCTION CheckFnctn()
RETURNS int
AS
BEGIN
DECLARE @retval int
SELECT @retval = COUNT(*) FROM CheckTbl
RETURN @retval
END;
GO
ALTER TABLE CheckTblADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );
GO
Just pass in columns A, B and C then return the boolean result of your calculation
October 30, 2006 at 4:53 pm
Yep, sounds good. Thanks.
November 1, 2006 at 10:22 am
CREATE
TABLE TestBits(
thekey
INT PRIMARY KEY,
A
BIT,
B
BIT,
C
BIT,
CONSTRAINT
ck_bits CHECK ((A | B ^ C) = 1))
INSERT
INTO TestBits VALUES (1, 1, 1, 1) -- fails
INSERT
INTO TestBits VALUES (2, 0, 1, 1) -- fails
INSERT
INTO TestBits VALUES (3, 1, 0, 1) -- fails
INSERT
INTO TestBits VALUES (4, 0, 0, 1) -- works
INSERT
INTO TestBits VALUES (5, 0, 0, 0) -- fails
INSERT
INTO TestBits VALUES (6, 1, 0, 0) -- works
INSERT
INTO TestBits VALUES (7, 0, 1, 0) -- works
INSERT
INTO TestBits VALUES (8, 1, 1, 0) -- works
November 1, 2006 at 1:40 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply