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:
[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[HideIfLoggedIn]
to be True if [RequireLogin]
is FalseYou 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))