SQL ENUM Example: Syntax, Uses, and Alternatives

How-to basics · Updated November 22, 2026

An SQL ENUM stores a value chosen from a predefined set of labels, such as draft, published, or archived. For example, in MySQL you can define a column as status ENUM('draft', 'published', 'archived') NOT NULL DEFAULT 'draft'; the exact syntax and support depend on the database system.

A MySQL ENUM example

Here is a complete MySQL table definition for articles with a status limited to three allowed labels:

CREATE TABLE articles ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(200) NOT NULL, status ENUM('draft', 'published', 'archived') NOT NULL DEFAULT 'draft' );

The ENUM declaration lists the permitted strings. The NOT NULL rule means every row must have a status, and the default supplies draft when an insert does not specify one. A valid insert can provide a label explicitly:

INSERT INTO articles (title, status) VALUES ('A first article', 'published');

Or it can omit the status and receive the default:

INSERT INTO articles (title) VALUES ('An unfinished article');

To retrieve articles in a particular state, use an ordinary equality condition:

SELECT title FROM articles WHERE status = 'published';

This example answers the basic question, but it is important not to assume that every database implements ENUM the same way—or supports the type at all.

How ENUM values work

An enum is a constrained set of named choices, rather than an unrestricted text field. In the MySQL definition above, the database accepts one of the declared labels for the column. That restriction can prevent accidental values such as publshed from being stored as if they were valid statuses. The labels are defined in the schema, so applications and database tools can inspect the permitted set.

MySQL also assigns each declared enum label a position in its list. For example, draft is first, published second, and archived third. MySQL uses these positions internally, and enum sorting follows the declared order rather than ordinary alphabetical order. This is useful when the sequence is intentional, but it can surprise someone expecting text sorting.

In MySQL, avoid treating those positions as the business meaning of a status. Write and read the labels, not numeric positions. Numeric values supplied in an enum context can be interpreted as position numbers, which makes queries and inserts less clear and can produce unintended results. A label such as 'published' expresses intent much better than a number.

An enum is also not a general-purpose workflow engine. It restricts the set of stored values, but it does not by itself enforce which transitions are allowed. If a record may move from draft to published but must never move from archived back to draft, that transition rule needs additional application or database logic.

PostgreSQL ENUM syntax

PostgreSQL supports enums too, but its syntax is different: create a named type first, then use that type for a column. For example:

CREATE TYPE article_status AS ENUM ('draft', 'published', 'archived'); CREATE TABLE articles ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title varchar(200) NOT NULL, status article_status NOT NULL DEFAULT 'draft' );

Then insert a row with a valid label in the usual way:

INSERT INTO articles (title, status) VALUES ('A first article', 'published');

PostgreSQL treats the enum as a separately named database type, which can be reused by multiple columns or tables. Its labels are case-sensitive, and the order declared when the type is created determines the enum ordering. That order can matter in comparisons and sorting, so choose it deliberately.

PostgreSQL allows labels to be added and labels to be renamed with appropriate ALTER TYPE commands. However, removing a label or changing the sort order is not a simple in-place edit; it typically involves creating a replacement type and migrating columns. Plan for that limitation if the set of choices is likely to change frequently.

Which databases support ENUM?

There is no single portable SQL enum definition that works unchanged across all popular database engines. MySQL and PostgreSQL have native enum types, but their syntax and operational details differ. MariaDB also supports an enum type with MySQL-style syntax, although exact behavior should be checked against the version and SQL mode in use.

SQL Server does not provide a native ENUM data type. A common equivalent is a text column protected by a CHECK constraint:

CREATE TABLE articles ( id int IDENTITY PRIMARY KEY, title varchar(200) NOT NULL, status varchar(20) NOT NULL CONSTRAINT CK_articles_status CHECK (status IN ('draft', 'published', 'archived')) );

SQLite likewise has no native enum type. It can use a TEXT column with a CHECK constraint to limit values. These alternatives enforce a similar rule, but they are not interchangeable with native enums in every detail. If an application supports multiple database engines, verify the schema and migration syntax for each one rather than relying on a type name that may not exist.

When an enum is a good choice

An enum often fits a short, stable set of values that is part of the data model. Examples include a fixed set of workflow states, a small set of priority labels, or a limited set of visibility options. It is most appealing when values rarely change and the database should reject invalid labels regardless of which application writes the row.

  • Use a native enum when the database supports it, the allowed choices are stable, and database-level validation is valuable.
  • Use a CHECK constraint when you want a simple fixed list but need a pattern supported by systems without native enums.
  • Use a lookup table when choices have extra attributes, need frequent edits, or should be managed as data rather than schema.

Think about the whole lifecycle, not just the initial table creation. If adding an option requires coordinated schema changes across several environments, a supposedly simple enum may create more deployment work than a lookup table. If a field is truly open-ended, such as a user-entered category name, an enum is the wrong tool: it would reject legitimate new values until the schema is changed.

Common pitfalls and changes

One frequent misconception is that an enum makes a value self-explanatory to every part of an application. It does not. A database can validate the stored label, but API code, reports, and user interfaces still need to agree on what each label means. A value such as archived should not silently acquire a different meaning in one part of the system.

Another issue is changing the allowed list after data already exists. In MySQL, adding a label generally involves altering the column definition. The exact command and operational impact depend on the server version, table characteristics, and change being made. Test the migration on a representative copy, check how the change is performed by your MySQL version, and account for locks or table work before applying it to a busy production system. In PostgreSQL, adding an enum value is supported, but removal and reordering need more involved migration work.

MySQL SQL mode is also relevant when invalid enum values are written. In strict mode, an invalid value normally causes an error. In a non-strict mode, MySQL can convert an invalid value to its special error value, associated with index zero, and issue a warning. Do not depend on permissive conversion: use an appropriate strict mode and check errors so a bad input cannot quietly become unexpected stored data.

Finally, beware of relying on enum order for presentation or business logic. MySQL sorts by the declaration order, while PostgreSQL also uses its declared enum order. If the desired display order changes, a query may need an explicit ordering rule, or the data model may be better represented with a separate numeric rank. Keep display preferences distinct from the meaning of a status unless they are genuinely the same rule.

ENUM compared with CHECK constraints and lookup tables

A native enum puts the set of labels directly into a database type or column definition. That is compact and provides strong validation, but changing the set is a schema operation and native syntax varies between database vendors. A CHECK constraint also keeps the accepted values in the schema, usually as an expression such as status IN ('draft', 'published', 'archived'). It is often a more portable idea, though constraint syntax and migration tools still differ.

A lookup table stores each allowed choice as a row, for example a statuses table with a key and display name. The articles table then references a status key through a foreign key. This approach takes more structure, but it supports additional data such as descriptions, display order, localization, or an enabled flag. It can also make it easier to add choices without altering a column type, though referential integrity and application behavior still need careful design.

A practical decision rule is to ask whether the options are structural constants or managed records. A handful of permanent states usually suit an enum or check constraint. Choices maintained by administrators, translated for users, or associated with their own metadata usually suit a lookup table. Portability requirements can also favor a check constraint or lookup table over vendor-specific enum syntax.

Frequently Asked Questions

Can an SQL ENUM column have a default?
Yes, in systems that support a native enum, a default can be set to one of its allowed labels. In the MySQL example, DEFAULT 'draft' fills in that value when an insert omits the column. A default does not make the column optional if it is also declared NOT NULL; it supplies a value for inserts that leave it out.

Is ENUM the same as a CHECK constraint?
No. Both can restrict values, but an enum is a database type or column type in systems that provide it, while a check constraint is a rule applied to a column or row. The exact behavior, ordering, and migration process differ by database. A check constraint is a common alternative when native enums are unavailable.

Can I add a new value to an ENUM?
Usually, but the method depends on the database. MySQL requires changing the column definition; PostgreSQL has a command to add a label to an enum type. Removing values or changing their order can be more difficult, so check the database-specific migration procedure before designing around frequent edits.

Does ENUM save space compared with VARCHAR?
It can have different storage characteristics, but storage is only one consideration and the details depend on the engine and schema. Do not choose an enum based on a blanket claim that it is always smaller or faster. Compare the actual database documentation and workload, and weigh migration complexity and portability as well.

Should I use ENUM for a status field?
It can work well for a small, stable status set when database-level validation is useful. If statuses change often, need descriptions or permissions, or must be managed without schema migrations, a lookup table may be a better fit. Also remember that an enum validates labels, not legal transitions between states.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.