在SQL Server中创建枚举 [英] Create enum in SQL Server

问题描述

我试图创建一个像在MySQL中一样的枚举

I've tried to create an enum like I would do in MySQL

USE WorldofWarcraft;

CREATE TABLE [users]
(
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   username varchar(255),
   password varchar(255),
   mail varchar (255),
   rank  ENUM ('Fresh meat', 'Intern','Janitor','Lieutenant','Supreme being')DEFAULT 'Fresh meat',
);

但是，我发现这在SQL Server中是不可能的，所以我的问题是，我怎么解决这个问题？

However, I've found out that this is not possible in SQL Server, so my question is, how would I get around this problem?

我已经阅读了这篇文章

等同于MySQL枚举数据类型的SQL Server？

建议类似

mycol VARCHAR(10) NOT NULL CHECK (mycol IN('Useful', 'Useless', 'Unknown'))

但是，我也无法正常工作我将如何创建默认值？

However, I can't get that to work, also, how would I create a default value?

最诚挚的问候。

编辑：枚举的目的是在该站点上有一个下拉列表，公会负责人可以在该站点上设置某人的排名。当用户创建一个帐户时，默认等级为新鲜肉类。

The purpose of the enum would be to have a dropdown on the site, where the guildmaster can set someone's rank. When a user creates an account the default rank is 'fresh meat'.

推荐答案

最好正确归一化模型：

create table user_rank
(
   id integer primary key, -- no identity, so you can control the values
   rank varchar(20) not null unique
);

insert into user_rank (id, rank)
values
  (1, 'Fresh Meat'),
  (2, 'Intern'),
  (3, 'Janitor'),
  (4, 'Lieutenant'),
  (5, 'Supreme being');

CREATE TABLE [users]
(
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   username varchar(255),
   password varchar(255),
   mail varchar (255),
   rank integer not null default 1, 
   constraint fk_user_rank foreign key (rank) references user_rank (id)
);

您可以通过查询 user_rank 表。

这篇关于在SQL Server中创建枚举的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！