Niranjan Godbole Niranjan Godbole - 4 years ago 116
SQL Question

How to assign 2 default values to SQL table column?

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 Source

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]
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download