How to assign 2 default values to SQL table column?

Question

I am designing user registration table with below columns.

CREATE TABLE [dbo].[NCT_UserRegistration]
(
    [User_Id] [int] IDENTITY(1,1) NOT NULL,
    [User_EmailId] [varchar](255) NULL,
    [User_Password] [varchar](512) NULL,
    [User_Name] [varchar](255) NULL,
    [User_MobileNum] [varchar](20) NULL,
    [User_Status] [varchar](15) NULL,
    [User_Role] [varchar](20) NULL,
    [User_CreatedDate] [timestamp] NULL,
    [User_UpdatedDate] [datetime] NULL,
    [Name] [varchar](30) NULL
)

My requirement for the status and role as below.

• status VARCHAR(15) Index, Enumeration of ENABLED, DISABLED.
• role VARCHAR(20) Enumeration of SUPER_ADMIN and PROJECT_ADMIN

What I understood from above is status should take only Enabled or Disabled and same with role also. How can I design my table to make sure it takes only those two values? Also is there any way for example if I supply 1 then it is ENABLED and 0 for DISABLED.

May I get some ideas here? Any help would be appreciated. Thank you

Answer 1

You need to use CHECK CONSTRAINT to limit to specific values

CREATE TABLE [dbo].[NCT_UserRegistration](
    [User_Id] [int] IDENTITY(1,1) NOT NULL,
    [User_EmailId] [varchar](255) NULL,
    [User_Password] [varchar](512) NULL,
    [User_Name] [varchar](255) NULL,
    [User_MobileNum] [varchar](20) NULL,
    [User_Status] [varchar](15) NULL CONSTRAINT chk_Status CHECK ([User_Status] IN ('ENABLED', 'DISABLED')),
    [User_Role] [varchar](20) NULL CONSTRAINT chk_Role CHECK ([User_Role] IN ('SUPER_ADMIN','DISABLED')),
    [User_CreatedDate] [timestamp] NULL,
    [User_UpdatedDate] [datetime] NULL,
    [Name] [varchar](30) NULL

)

For enumeration you will have to handle at front end or while retriving values from table which is an extra step.

SELECT CASE WHEN [User_Status] = 1 THEN 'ENABLED' WHEN [User_Status] = 0 THEN 'DISABLED' END As UserStratus
FROM [dbo].[NCT_UserRegistration]