Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 533
HeavenCore Avatar asked Mar 10 '13 23:03

HeavenCore


1 Answers

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))
like image 152
RThomas Avatar answered Sep 22 '22 12:09

RThomas