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

Kristenkristi

(1) [RequireAdmin] <= [RequireLogin]? (2) [RequireLogin] = 0 OR [HideIfLoggedIn] = 1? – Manmade

Answer 1

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

Emergency

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

Good, that combined boolean logic is always easy to get backwards. – Emergency

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

Ultramodern

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. – Confirmation

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. – Ultramodern

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. – Confirmation

Good catch on adding constraint as an alter table - I hadn't thought that through originally. Thanks. – Emergency