ENUM in SQL defines a column whose values must be drawn from a fixed list. It’s compact, fast, and acts as a built-in constraint — nothing outside the declared list can ever be inserted.
ENUM is not standard SQL and behaves differently across dialects. MySQL has the inline ENUM('a', 'b', 'c') column type shown below. PostgreSQL requires a separate CREATE TYPE mood AS ENUM ('happy', 'sad'); step before the type can be used in a column declaration, and adding a new value uses ALTER TYPE ... ADD VALUE. SQLite has no native enum at all — the equivalent is a CHECK (status IN ('P', 'F')) constraint. SQL Server has no enum type either; the standard idiom there is a CHECK constraint or a lookup table with a foreign key. Code written assuming the MySQL syntax does not port.
ALTER TABLE students
ADD COLUMN student_status ENUM('P', 'F') AFTER family_name;This says student_status must be 'P' (part-time) or 'F' (full-time). Any insert attempting any other value will fail. The database stores ENUM values internally as small integer indices into the value list, which is why ENUM is more space-efficient than the equivalent VARCHAR.
ENUM is the right type when the set of possible values is:
- Known in advance — categories like
'pending' | 'in_progress' | 'completed', sizes like'S' | 'M' | 'L' | 'XL', account types like'free' | 'paid' | 'enterprise'. - Small — typically under a few dozen values. For thousands of possible values, a separate lookup table with a Foreign key is usually better.
- Stable — adding a new value to an ENUM requires an
ALTER TABLE, which can be expensive on large tables.
ENUM has detractors: it ties the schema to a specific set of values that may need to change, the alteration cost grows with table size, and porting to a different database (PostgreSQL has its own enum type with different syntax) can be awkward. For these reasons, many engineers prefer a separate lookup table with a foreign-key constraint, which trades a small amount of storage and join cost for more flexibility. ENUM is a reasonable choice when the set is genuinely small and stable; the foreign-key approach is the safer default when it isn’t.
ENUM is part of the SQL string-types family, alongside CHAR and VARCHAR.