Check Constraint – only allow one column to be true if another column is true

Question

Take the following example table:

CREATE TABLE [dbo].[tbl_Example](
    [PageID] [int] IDENTITY(1,1) NOT NULL,
    [RequireLogin] [bit] NOT NULL,
    [RequireAdmin] [bit] NOT NULL,
    [HideIfLoggedIn] [bit] NOT NULL
)

How would one rewrite the above to include check constraints as follows:

Force [RequireAdmin] to be False if [RequireLogin] is False (i.e only allow [RequireAdmin] to be True if [RequireLogin] is True whilst allowing [RequireLogin] to be True and [RequireAdmin] to be False
Only allow [HideIfLoggedIn] to be True if [RequireLogin] is False

(1) [RequireAdmin] <= [RequireLogin]? (2) [RequireLogin] = 0 OR [HideIfLoggedIn] = 1? — wqw, Mar 10 '13 at 23:57

RThomas · Accepted Answer · 2015-04-09T21:00:03.247

You typically do nested case statements in the check in order to get that type of logic to work. Remember that a case in a check must still be an evaluation, so it would take the form

CHECK (case when <exp> then 1 end = 1).

Looking over your exact requirements however it seems that this would also work and is probably easier to read:

 CREATE TABLE [dbo].[tbl_Example]
 (
    [PageID] [int] IDENTITY(1,1) NOT NULL,
    [RequireLogin] [bit] NOT NULL,
    [RequireAdmin] [bit] NOT NULL,
    [HideIfLoggedIn] [bit] NOT NULL
 )

 ALTER TABLE [dbo].[tbl_Example] ADD CONSTRAINT
     [RequireAdmin] CHECK 
      ((RequireAdmin = RequireLogin) OR 
      (RequireLogin=1));

 ALTER TABLE [dbo].[tbl_Example] ADD CONSTRAINT
      [HideIfLoggedIn] CHECK 
      ((RequireLogin=1 AND HideIfLoggedIn=0) OR 
       (RequireLogin=0 AND HideIfLoggedIn=1) OR 
       (RequireLogin=0 AND HideIfLoggedIn=0))

Had to change that last one to `((RequireLogin=1 AND HideIfLoggedIn=0) OR (RequireLogin=0 AND HideIfLoggedIn=1) OR (RequireLogin=0 AND HideIfLoggedIn=0))` - but yes, works great - cheers — HeavenCore, Mar 11 '13 at 00:32
Good, that combined boolean logic is always easy to get backwards. — RThomas, Mar 11 '13 at 01:36

Greenstone Walker · Answer 2 · 2013-03-11T00:45:32.713

2

If I have the algebra correct:

alter table dbo.Example
add constraint RequireAdmin_RequireLogin_ck
check ( not ( RequireAdmin = 'true' and RequireLogin = 'false' ) ) ;
alter table dbo.Example
add constraint HideIfLoggedIn_RequireLogin_ck
check ( not ( HideIfLoggedIn = 'true' and RequireLogin = 'true' ) ) ;

Note that these check constraints are defined at the table level not the column level, as they must because they reference multiple columns. RThomas' answer is not valid Transact-SQL for this reason!

edited Mar 11 '13 at 00:45

answered Mar 11 '13 at 00:28

Greenstone Walker

1,090
9
8

I recommend two things: (1) trying not to enforce constraints using `NOT` - it just reverses the way you have to process things mentally (2) using 0 and 1 instead of boolean-style string literals for BIT comparisons. – Aaron Bertrand Mar 11 '13 at 00:33
I disagree with you on both of those, Aaron. First, organisational policies are usually expressed as negatives (e.g. "employees are not allowed to surf ebay during work hours") so creating the constraints using the same wording gives better reading comprehension. Second, these are booleans - they are only a bit field because MSSQL doesn't have a boolean data type. Using 'true' is better for reading comprehension because it explicitly conveys "true" to the reader. By contrast, 0 and 1 require the reader to remember whether 0 represents false or true in this particular language. – Greenstone Walker Mar 11 '13 at 00:37
3

Well, do what you want, of course. I can tell you from 16 years of experience in SQL Server that the two things I mentioned *will* be confusing to most users, regardless of how much sense they might make to you. – Aaron Bertrand Mar 11 '13 at 01:33
Good catch on adding constraint as an alter table - I hadn't thought that through originally. Thanks. – RThomas Apr 09 '15 at 20:55

Check Constraint – only allow one column to be true if another column is true

2 Answers2