Geek asks and answers

Check constraint - allow only one column if the other column is correct

Take the following sample 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
)

As can be rewritten above to include control restrictions as follows:

  • > True , allowing to [RequireLogin]be True and [RequireAdmin] False
  • Allow [HideIfLoggedIn] True if [RequireLogin] False
+5
source share
2 answers

Usually you do nested case statements in a check to make this type of logic work. Remember that the case on the check should still be an estimate, so it will take the form

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

Looking back at your exact requirements, it looks like this will 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))
+6

:

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

, , , , . RThomas Transact-SQL !

+2

More articles:


All Articles