如何为SQL表列分配2个默认值？

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.

status VARCHAR(15)索引,ENABLED枚举,禁用。

role VARCHAR(20) Enumeration of SUPER_ADMIN and PROJECT_ADMIN

role VARCHAR(20)SUPER_ADMIN和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.

我从上面了解到的状态应该只是启用或禁用,也与角色相同。如何设计我的表以确保它只需要这两个值?也有任何方式,例如,如果我提供1然后它是ENABLED和0为DISABLED。

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

我可以在这里得到一些想法吗?任何帮助,将不胜感激。谢谢

3 个解决方案

#1

You need to use CHECK CONSTRAINT to limit to specific values

您需要使用CHECK CONSTRAINT来限制特定值

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]

#2

Can you try adding constraint as below for status field. If its working then apply the same to ROLE field.

您可以尝试为状态字段添加以下约束。如果它正在工作,则将其应用于ROLE字段。

alter table NCT_UserRegistration
    add (STATUS VARCHAR(15) default 'ENABLED',
         constraint conSTATUS check (STATUS in ('ENABLED', 'DISABLED')))

#3

There are two possible approaches.

有两种可能的方法。

Check constraints - @mh2017 has explained this well in his answer.

检查约束 - @ mh2017在他的回答中解释了这一点。

Looking at the conversation that seems to fit your requirements better, but just for the sake of sharing idea, I will mention -

看看似乎更符合您要求的对话,但仅仅是为了分享想法,我会提到 -

Foreign key constraint (FK)- If it is acceptable to modify the User_Status and User_Role columns to be of type tinyint (or similar number type), you can store just the ids in these and create enumeration (aka mapping tables) to store what the ids represent. Create FK on User_Status and User_Role in NCT_UserRegistration to refer to the enumeration tables.

外键约束(FK) - 如果可以将User_Status和User_Role列修改为tinyint类型(或类似的数字类型),则可以只在这些列中存储id并创建枚举(也就是映射表)来存储ids代表。在User_Status上创建FK,在NCT_UserRegistration中创建User_Role以引用枚举表。

FKC ensures that the referring column (User_Status and User_Role in NCT_UserRegistration) cannot have value other than those listed in the referred to column (the respective id columns in the enumeration tables)

FKC确保引用列(NCT_UserRegistration中的User_Status和User_Role)不能具有除引用列中列出的值之外的值(枚举表中的相应id列)

This Foreign key vs check constraint for integrity post also describes few benefits of using FK over check constraint

此完整性帖子的外键与检查约束也描述了使用FK超过检查约束的一些好处

Here is a sample code showing foreign key approach

以下是显示外键方法的示例代码

  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] tinyint NULL, -- I changed this from varchar to tinyint
    [User_Role] tinyint  NULL, -- I changed this from varchar to tinyint
    [User_CreatedDate] [timestamp] NULL,
    [User_UpdatedDate] [datetime] NULL,
    [Name] [varchar](30) NULL
)

create table StatusEnumeration
(
    StatusId tinyint,
    Description varchar(10)
    constraint pk_StatusEnumeration__StatusId primary key clustered (StatusId)
)

insert into StatusEnumeration(StatusId, Description)
values
(0, 'Disabled'),
(1, 'Enabled')


create table RoleEnumeration
(
    RoleId tinyint,
    Description varchar(20)
    constraint pk_RoleEnumeration__RoleId primary key clustered (RoleId)
)

insert into RoleEnumeration(RoleId, Description)
values
(0, 'SUPER_ADMIN '),
(1, 'PROJECT_ADMIN')

alter table NCT_UserRegistration
add constraint fk_NCT_UserRegistration__StatusEnumeration_StatusId foreign key (User_Status)
    references StatusEnumeration (StatusId)
go

alter table NCT_UserRegistration
add constraint fk_NCT_UserRegistration__RoleEnumeration_RoleId foreign key (User_Role)
    references RoleEnumeration (RoleId)
go

#1