ENUM TYPE

Data Types
Buy Me A Coffee

A column type that restricts values to a predefined set of named string constants, enforced at the database level.

A set of named values representing a fixed set of choices, like order_status (pending, shipped, delivered). More self-documenting than integer codes. PostgreSQL and MySQL support enums natively; SQL Server uses CHECK constraints with a values table instead.

Compatibility

Filter by Database
SQL ENUM TYPE Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native 5.0 ENUM is a native column type. Values are stored as integers internally (1-indexed position). Adding values requires ALTER TABLE. Comparing ENUM values uses integer order, not lexicographic.
PostgreSQL Native 8.3 CREATE TYPE ... AS ENUM creates a reusable named type. Values can be added with ALTER TYPE ... ADD VALUE but cannot be removed. Enum ordering reflects declaration order, not alphabetical.
SQL Server Not Supported - No native ENUM type. Use a CHECK constraint or a foreign key to a lookup table.
Oracle Not Supported - No native ENUM type. Use a CHECK constraint or a lookup table with a foreign key.
SQLite Not Supported - No native ENUM type. Use a CHECK constraint to restrict allowed values.

Details

MySQL and PostgreSQL have native ENUM support; SQL Server, Oracle, and SQLite do not, relying on CHECK constraints or lookup tables. The two native implementations differ significantly: MySQL stores ENUMs as integers internally, so values sort by declaration position rather than alphabetically, and reordering values is a breaking schema change. PostgreSQL's named CREATE TYPE approach is more maintainable — the type is reusable across tables and extensible. A key limitation in PostgreSQL: enum values cannot be removed or renamed after creation without dropping and recreating the type. For teams targeting multiple engines, CHECK constraints or a lookup table with a foreign key are the portable alternatives, with the lookup-table approach offering easier value set changes over time.

Standard Syntax

-- MySQL CREATE TABLE orders ( status ENUM('pending', 'shipped', 'delivered') );

Version Support

MySQL: Native since 5.0 PostgreSQL: Native since 8.3 SQL Server: Not supported Oracle: Not supported SQLite: Not supported

Per-Database Syntax & Notes

MySQL Native syntax

ENUM is a native column type. Values are stored as integers internally (1-indexed position). Adding values requires ALTER TABLE. Comparing ENUM values uses integer order, not lexicographic.

CREATE TABLE orders ( status ENUM('pending', 'shipped', 'delivered') NOT NULL ); SELECT * FROM orders WHERE status = 'shipped';

PostgreSQL Native syntax

CREATE TYPE ... AS ENUM creates a reusable named type. Values can be added with ALTER TYPE ... ADD VALUE but cannot be removed. Enum ordering reflects declaration order, not alphabetical.

CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered'); CREATE TABLE orders (status order_status NOT NULL); -- Add a value: ALTER TYPE order_status ADD VALUE 'cancelled';

SQL Server Alternative syntax

No native ENUM type. Use a CHECK constraint or a foreign key to a lookup table.

CREATE TABLE orders ( status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'shipped', 'delivered')) );

Oracle Alternative syntax

No native ENUM type. Use a CHECK constraint or a lookup table with a foreign key.

CREATE TABLE orders ( status VARCHAR2(20) NOT NULL CHECK (status IN ('pending', 'shipped', 'delivered')) );

SQLite Alternative syntax

No native ENUM type. Use a CHECK constraint to restrict allowed values.

CREATE TABLE orders ( status TEXT NOT NULL CHECK (status IN ('pending', 'shipped', 'delivered')) );