Sql Server equivalent to MySQL enum data type?

sql

data-integrity

check-constraints

enum-data-type

byNikita Barsukov·Oct 10, 2024

In SQL Server, there isn't a native ENUM type. However, it can be emulated with a reference table for allowed values and a foreign key in your main table to enforce this constraint, hence creating a mechanism that restricts data to only predefined values.

For instance:

-- Assemble/set up a table with allowed values
CREATE TABLE ColorEnum (Color VARCHAR(20) PRIMARY KEY);

-- Don't fear adding colours, this isn't a laundry machine
INSERT INTO ColorEnum (Color) VALUES ('Red'), ('Green'), ('Blue');

-- Principal/main table with a 'Color' column 
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Color VARCHAR(20) CONSTRAINT FK_ColorEnum FOREIGN KEY REFERENCES ColorEnum(Color)
);

This structure guarantees that the values in Products.Color align with those specified in ColorEnum, much like an ENUM.

Emulating ENUM with CHECK constraint

If you prefer leaner schemas, you can emulate ENUM by applying a CHECK constraint to your column, which directly defines allowed values without having to set up a new table.

-- Principal/main table with 'Color' column and CHECK constraint
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Color VARCHAR(20) NOT NULL CHECK (Color IN ('Red', 'Green', 'Blue'))  -- Trust me, aliens prefer these colors
);

CHECK constraints ensure data integrity and establish rules through the table definition, thereby ensuring that the column carries only the permitted values.

Enum-like structures in Practice

While modeling "enum-like" structures, consider the following trade-offs:

Performance: Check constraints can perform better as they negate the obligation for a join.
Maintenance: An independent table simplifies updating the list of allowable values, avoiding changes to the table schema.
Data integrity: Lookup tables with foreign keys are normalized and guarantee data integrity, which is unlike check constraints.
Integration: .NET enums can be auto-generated from lookup tables using T4 templates, enhancing type safety in application layers.

Advanced enum-like implementations

When complex architectures call for more robust enumeration, offering additional metadata, the following advanced strategies are useful.

CREATE TABLE ColorEnum (
    ColorID INT PRIMARY KEY,
    ColorName VARCHAR(15) NOT NULL,  -- Unique paint names, not your average art book
    Description VARCHAR(255)
);

ALTER TABLE Products
    ADD COLUMN ColorID INT CONSTRAINT FK_ColorEnum FOREIGN KEY REFERENCES ColorEnum(ColorID);

This allows for extra context for each color like descriptions and usage guidelines.

Building dynamic enumeration functions

CREATE FUNCTION dbo.GetColors()
RETURNS TABLE
AS
RETURN (
    SELECT Color
    FROM (VALUES ('Red'), ('Green'), ('Blue')) AS ColorSet(Color)  -- No, these are not Power Rangers
);