1

I would like to know if it's possible to have two fields (in this case bits) in the same table where one is dependent on another. For example Check2 may only be true if Check1 is true.

Note: I'm trying to implement a constraint in an attempt to accomplish this, but so far failing. I admit I have limited experience with constraints. This is a new database with little data, and there are no entries currently in the table where the conditions aforementioned are not true. That is, there is no record where Check2 is true.

Hannah Vernon
  • 66,859
  • 22
  • 158
  • 297
Chiramisu
  • 273
  • 3
  • 12

1 Answers1

3

You can use a CHECK constraint with a CASE expression. If check2 is enabled, make sure check1 is enabled too, otherwise you can default to a no-op ("checking" that check1 = check1). For simplicity I'm assuming these columns are not NULLable.

CREATE TABLE dbo.foo
(
  check1 BIT NOT NULL DEFAULT 0,
  check2 BIT NOT NULL DEFAULT 0,
  CONSTRAINT ck_both CHECK (check1 = CASE WHEN check2 = 1 THEN 1 ELSE check1 END)
);

INSERT dbo.foo DEFAULT VALUES;             -- 0,0 succeeds
INSERT dbo.foo(check1,check2) VALUES(1,0); -- succeeds
INSERT dbo.foo(check1,check2) VALUES(1,1); -- succeeds
GO
INSERT dbo.foo(check1,check2) VALUES(0,1); -- fails

To add this constraint to your existing table:

ALTER TABLE dbo.tablename
  ADD CONSTRAINT ck_BothBits
  CHECK (check1 = CASE WHEN check2 = 1 THEN 1 ELSE check1 END);

If the columns are NULLable (hopefully they aren't), you can say something like this (untested):

ALTER TABLE dbo.tablename
  ADD CONSTRAINT ck_BothBits
  CHECK (COALESCE(check1,0) = CASE 
    WHEN check2 = 1 THEN 1 ELSE COALESCE(check1,0) END);

With that constraint in place:

INSERT dbo.foo(check1,check2) VALUES(NULL,NULL) -- succeeds
INSERT dbo.foo(check1,check2) VALUES(1,0);      -- succeeds
INSERT dbo.foo(check1,check2) VALUES(1,1);      -- succeeds
INSERT dbo.foo(check1,check2) VALUES(1,NULL);   -- succeeds
INSERT dbo.foo(check1,check2) VALUES(0,NULL);   -- succeeds
INSERT dbo.foo(check1,check2) VALUES(NULL,0);   -- succeeds
GO
INSERT dbo.foo(check1,check2) VALUES(0,1);      -- fails
GO
INSERT dbo.foo(check1,check2) VALUES(NULL,1);   -- fails
Aaron Bertrand
  • 175,645
  • 25
  • 380
  • 589
  • Nice answer! The constraint can also be written as CONSTRAINT ck_both CHECK (check2 = check1 & check2 ) ..you know for the geeks :) – Mister Magoo Mar 02 '15 at 20:19
  • Brilliant, this is precisely what I needed, thank you. Your truth table is exactly what I wanted and yes of course my bits are non-nullable. Cheers! :) – Chiramisu Mar 02 '15 at 20:28
  • @MisterMagoo I don't find that all that intuitive (I have a thing against bitwise in general anyway), plus it allows `NULL,1` if the columns are, in fact, `NULL`able. – Aaron Bertrand Mar 02 '15 at 20:31
  • @AaronBertrand fair enough, I found the CASE similarly difficult to grasp - people are different. Also, yes if NULL is an option, then it doesn't work. – Mister Magoo Mar 02 '15 at 20:44