24

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.

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
  • 1
    You could add a [default constraint](https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-default-values-for-columns?view=sql-server-2017) – jpw Sep 07 '18 at 12:47
  • 1
    It depends what you want to do with the enumerated type. For instance, if you are depending on the ordering for sorting, then a `check` constraint isn't sufficient. – Gordon Linoff Sep 07 '18 at 12:48
  • 5
    Honestly I think it's [best to ignore ENUM types](http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/) completely and handle validation in your application, not way way down in your database. Perhaps this is a blessing in disguise. – JNevill Sep 07 '18 at 12:51
  • 1
    Yeah, no idea what you're asking. Describe what you are trying to achieve without using the word `ENUM`. – Tab Alleman Sep 07 '18 at 12:51
  • 1
    As for your second question about column default values [you can find more info here](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-2017#default-definitions) – JNevill Sep 07 '18 at 12:53
  • 8
    I would use an INT value instead, and make it a foreign key to a [Rank] table with columns like ID and Description. – Peter B Sep 07 '18 at 12:57
  • 3
    Based on your edit, it seems you simply need a domain table (often casually called a lookup table), referenced by the users table with a foreign key. Rather than an `IDENTITY`, use a value that you control so that you can maintain an enum in your app code that's always synced with the database. That table could also include an indicator of which row is the desired default value. – Dan Guzman Sep 07 '18 at 13:10
  • 2
    I also agree foreign key approach would better, but if you need anyway, this is syntax for check constraint: `... rank varchar(250) NOT NULL CONSTRAINT check_rank CHECK (rank IN('Fresh meat', 'Intern','Janitor','Lieutenant','Supreme being') ) DEFAULT 'Fresh meat' ...` – Oto Shavadze Sep 07 '18 at 13:15
  • 1
    @JNevill "way down in your database"? Validating all the way up in the app and not in the database is like the [application tail wagging the database dog](https://www.red-gate.com/simple-talk/sql/database-administration/five-simple-database-design-errors-you-should-avoid/#:~:text=Application%20Tail%20wagging%20the%20Database%20Dog). Not a good idea because then you could write any old junk into the database using a different client/app, and cause your app to crash when the enum value is not in range. The database is not just a good looking text file! – Reversed Engineer Mar 24 '21 at 09:56
  • @reversedengineer I'm all for FK, constraints, and whatnot on the database. I'm a Data Engineer by trade so my usual advice isn't "do it in the application" layer, but I feel that enum should be owned application side and not in the database. It feels like the database tail wagging the application dog, to borrow your linktext. If you come to me and say I have to run a DDL because you want a new value in your UI drop down, that ALTER is going to be converting the enum to a varchar because that's some nonsense. – JNevill Mar 28 '21 at 01:43

2 Answers2

34

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.

  • I would add that the id values be flag values such as (`0`, `1`, `2`,`4`,`8`,`16` ... ) which would allow the graphical program to `and` and `or` the values for a multi-selection. Also in some cases use `0` as the default value for C# would automatically associate an instantiation with that by default; depending on the situation. – ΩmegaMan Jan 29 '22 at 14:45
  • This answer post was just the topic of [this question post](https://dba.stackexchange.com/q/311041/43932) with regard to the role "properly normalize" plays here. PS Replacing values by others that are supposedly somehow more id-ish or supposedly somehow better at identifying them than they identify themselves is not either of the 2 related things meant by database normalization. (To a 1NF or to higher NFs.) (I was surprised to now see this username on this post, I would have thought you knew that.) – philipxy Apr 17 '22 at 06:34
  • 1
    Hard disagree. This does absolutely nothing for normalization (since enum does this in a better way under the hood), it just makes the int to string table used to store it in a compressed formal mutable instead of immutable, so that the query optimizer always has to assume it can mutate in the middle of a transaction. – saolof Jan 17 '23 at 12:47
10

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'
);
CostaIvo
  • 1,029
  • 13
  • 19