CODEWITHSUNDEEP
sqlsql-serverenums
user9807244
user9807244

Reputation:

Create enum in SQL Server

In MySQL one can create an enum as such:

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',
);

This is not possible in SQL Server, so what are the alternatives?

I've read this post

SQL Server equivalent to MySQL enum data type?

Suggesting something like

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

How can one get that work and create a default value?

The purpose of the enum would be able to tie it to a graphical dropdown on the site which presents the user with values and has a default value pre-specified.

Upvotes: 33

Views: 127587

Answers (2)

CostaIvo
CostaIvo

Reputation: 1239

There is no enum datatype available in SQL Server like in MySQL.

But using the CHECK constraint enum functionality can be implemented.

USE WorldofWarcraft;

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

Upvotes: 17

user330315
user330315

Reputation:

It's better to properly normalize your model:

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)
);

The dropdown on your web site can easily be populated by querying the user_rank table.

Upvotes: 36

Related Questions