Complete Database Enum Guide

Creating Enum Types

-- Create enum type
CREATE TYPE order_status AS ENUM (
  'pending',
  'processing', 
  'shipped',
  'delivered',
  'cancelled'
);

-- Use in table
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  status      order_status NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Query examples
SELECT * FROM orders WHERE status = 'pending';
SELECT status, COUNT(*) FROM orders GROUP BY status;

Enum types provide type safety, high storage efficiency (internally uses integers), and good query performance.

Adding Enum Values

-- Add new value at the end
ALTER TYPE order_status ADD VALUE 'refunded';

-- Add at specified position
ALTER TYPE order_status ADD VALUE 'confirmed' 
  AFTER 'pending';

ALTER TYPE order_status ADD VALUE 'failed' 
  BEFORE 'cancelled';

-- Note: ADD VALUE cannot be executed in a transaction
-- Needs to be run separately in migration script

PostgreSQL 9.1+ supports ADD VALUE, but it cannot be executed within a transaction block.

Query All Enum Types

-- List all enum types
SELECT n.nspname AS schema,
       t.typname AS enum_type,
       e.enumlabel AS enum_value
FROM pg_type t 
JOIN pg_enum e ON t.oid = e.enumtypid  
JOIN pg_namespace n ON t.typnamespace = n.oid
ORDER BY schema, enum_type, e.enumsortorder;

Check Data Consistency

-- Check for invalid values (should not exist in theory)
SELECT DISTINCT status 
FROM orders 
WHERE status NOT IN (
  SELECT enumlabel::order_status 
  FROM pg_enum 
  WHERE enumtypid = 'order_status'::regtype
);

SQLite CHECK Constraint

-- Use CHECK constraint to simulate enum
CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK(status IN (
      'pending',
      'processing',
      'shipped',
      'delivered',
      'cancelled'
    )),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Attempting to insert invalid value will fail
INSERT INTO orders (status) VALUES ('invalid');
-- Error: CHECK constraint failed

CHECK constraints provide basic value validation, but are not as powerful as true enum types.

SQL Server CHECK Constraint

-- SQL Server also uses CHECK constraint
CREATE TABLE orders (
  id INT IDENTITY(1,1) PRIMARY KEY,
  status VARCHAR(20) NOT NULL DEFAULT 'pending'
    CONSTRAINT CK_order_status 
    CHECK (status IN (
      'pending',
      'processing',
      'shipped',
      'delivered',
      'cancelled'
    )),
  created_at DATETIME2 DEFAULT GETDATE()
);

-- Modify constraint (add new value)
ALTER TABLE orders DROP CONSTRAINT CK_order_status;
ALTER TABLE orders ADD CONSTRAINT CK_order_status
  CHECK (status IN (
    'pending', 'processing', 'shipped', 
    'delivered', 'cancelled', 'refunded'
  ));