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