To create an enum in SQL, define a column that accepts only a fixed set of values—but the syntax depends on your database. PostgreSQL supports a native enum type, MySQL offers an ENUM column type, and SQL Server and SQLite typically use a CHECK constraint or a related lookup table. First identify your database and version, then choose the approach that fits how often the allowed values may change.
What You’ll Need
- Access to a SQL database and permission to create or alter tables and types.
- The database product and version: PostgreSQL, MySQL, SQL Server, and SQLite do not share one universal enum syntax.
- A short, stable list of allowed values, such as order statuses. Decide how new values will be managed before using the list in a production table.
- A development or test database for trying the statements before applying schema changes to important data.
Step-by-Step
- Choose the database-specific design. SQL does not provide one portable CREATE ENUM statement that works in every database. PostgreSQL lets you create a reusable enum type. MySQL lets you declare ENUM directly on a column. SQL Server and SQLite can restrict a text column with a CHECK constraint, or use a lookup table when values need to be managed as data. If you expect the list to change often, or need descriptions, translations, or per-value settings, a lookup table is usually easier to maintain than a native enum.
- For PostgreSQL, create a named enum type. Run this once in the target schema, changing the type name and values to suit your application:
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');The strings are the allowed labels. PostgreSQL treats these as distinct values, so spelling and capitalization matter. The enum’s sort order is the order in which its values are listed; that ordering can affect comparisons and sorting. Do not assume it is alphabetical.
- Use the PostgreSQL type in a table. Declare the column with the type name, then add a default only if new rows should receive one automatically:
CREATE TABLE orders ( order_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, status order_status NOT NULL DEFAULT 'pending' );The NOT NULL rule prevents missing statuses, while the default supplies ‘pending’ when an insert leaves the column out. Insert and query values as ordinary quoted SQL string literals:
INSERT INTO orders (status) VALUES ('paid'); SELECT order_id, status FROM orders WHERE status = 'paid';An attempt to insert a label that was not declared, such as ‘returned’, fails instead of silently storing it. If the table already exists, add a column with an appropriate migration rather than rerunning CREATE TABLE.
- For MySQL, define ENUM on the column. MySQL does not use PostgreSQL’s separate CREATE TYPE syntax. Put the allowed labels in the column definition instead:
CREATE TABLE orders ( order_id integer NOT NULL AUTO_INCREMENT PRIMARY KEY, status ENUM('pending', 'paid', 'shipped', 'cancelled') NOT NULL DEFAULT 'pending' );MySQL ENUM values are declared as strings, but internally they also have numeric positions based on declaration order. Use the labels in application queries, not numeric positions, and avoid relying on the enum’s implicit sorting behavior. Check the server’s SQL mode and application behavior so an invalid value cannot be silently converted or mishandled.
- For SQL Server or SQLite, add a CHECK constraint. Neither provides the same native enum type as PostgreSQL. A constraint can restrict a text column to a fixed list. In SQL Server, for example:
CREATE TABLE dbo.Orders ( OrderId int IDENTITY(1,1) PRIMARY KEY, Status varchar(20) NOT NULL CONSTRAINT DF_Orders_Status DEFAULT ('pending'), CONSTRAINT CK_Orders_Status CHECK (Status IN ('pending', 'paid', 'shipped', 'cancelled')) );In SQLite, the equivalent idea is:
CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')) );A CHECK constraint makes invalid values fail at the database level, including values inserted by scripts or other applications. In SQL Server, consider a case-insensitive or case-sensitive collation deliberately; comparisons follow the column’s collation. SQLite’s comparison behavior can also depend on collation settings.
- Test both accepted and rejected values. Insert a row with a permitted label and confirm it succeeds. Then try a deliberately invalid label in a test transaction or disposable table and confirm the database rejects it. Check that the default works when the status column is omitted, and that NULL is rejected if the column is intended to be mandatory. Also test application code that reads and writes the column; a database constraint does not automatically update validation lists in your application.
- Plan how the list will evolve. PostgreSQL can add a value with
ALTER TYPE order_status ADD VALUE 'refunded';. Adding a value does not provide a simple way to remove or reorder existing labels; a larger redesign may require creating a replacement type and migrating columns. For MySQL, changing an enum commonly means altering the column and restating the complete allowed list, for example with ALTER TABLE and MODIFY COLUMN. Preserve the existing nullability and default when doing so. With CHECK constraints, update the constraint as part of a migration. Test any change against existing rows and application releases before deploying it.
Common Mistakes
Using syntax from the wrong database: PostgreSQL’s CREATE TYPE … AS ENUM statement is not portable to MySQL, SQL Server, or SQLite. Confirm the database engine before copying a sample.
Choosing an enum for a changing list: A workflow may gain new states, or administrators may need to define options. Repeated schema migrations can become cumbersome. A lookup table with a foreign key is often a better fit for values that are frequently added, disabled, or described with extra fields.
Forgetting defaults and nullability: An enum restricts which non-null values are allowed, but it does not by itself require a value or decide what a new row should use. Add NOT NULL and a DEFAULT only when they match the data rules.
Changing labels without migrating stored data: Renaming a status in application code alone can leave old rows using the previous label. Coordinate database changes, existing data, and application versions. Pay close attention to capitalization and whitespace, since labels that look similar may not compare as equal.
Assuming declaration order is harmless: PostgreSQL enums have declaration-based ordering, while MySQL also assigns enum positions. Avoid using those positions as business priority. If the ordering is meaningful and likely to change, store an explicit sort order in a lookup table.
Tips
- Choose concise, stable labels that describe stored states, such as ‘pending’ or ‘shipped’. Keep user-facing text and translations in the application or a separate table rather than baking presentation wording into a database type.
- Use consistent naming for types and constraints. Descriptive names make schema errors and migrations easier to understand.
- Keep the allowed values in source-controlled migrations. That gives teammates and deployment environments a repeatable record of schema changes.
- For a lookup-table design, create a table of permitted statuses and reference it with a foreign key. You can then store descriptions, display order, or an active flag without changing a type definition.
- Apply schema changes first in a development or staging environment, and make sure existing rows remain valid before tightening a constraint.
Troubleshooting
The database reports a syntax error near ENUM or TYPE. Check which engine is running and use its syntax. A PostgreSQL type declaration will not work as a MySQL column definition, and SQL Server needs a CHECK constraint or lookup-table design instead.
An insert says the value is invalid. Compare the submitted value with the declared labels character by character. Check spelling, capitalization, trailing spaces, and whether the migration that adds the value has run in this database. If an application sends a different label from the one in its validation list, update the application and schema together.
A migration fails when adding or changing a value. Check existing rows, dependent columns, and the database’s rules for altering the type or constraint. PostgreSQL has restrictions around when a newly added enum value can be used within a transaction; commit the change before using that value where required by your server version. For MySQL, include the full intended ENUM list and preserve the column’s default and nullability when modifying it. Back up important data and test the migration before production.
Invalid values appear to be accepted. Verify that the intended constraint exists on the correct table and column, and inspect database modes and client behavior. In MySQL, strict SQL mode is important for detecting invalid enum input rather than allowing surprising conversions. Test by inserting a clearly invalid value directly through a SQL client, not only through the application’s form.
Existing rows prevent a constraint change. Find values outside the proposed allowed list, decide how each should be mapped, and update those rows before enforcing the new rule. Do not discard or rewrite production values without confirming the intended business meaning.
Frequently Asked Questions
Is ENUM part of standard SQL?
There is no single standard CREATE ENUM syntax supported consistently across the major database systems. Use the feature or constraint supported by your specific database, and use a lookup table when portability is important.
Can I add a new enum value later?
Usually, but the operation differs by database. PostgreSQL has ALTER TYPE, MySQL requires altering the column definition, and CHECK constraints must be revised. Plan and test the migration, especially if applications are deployed at the same time.
Can an enum column be NULL?
Yes, unless the column is declared NOT NULL. A NULL is not the same as an additional allowed label; it means no value is present. Add NOT NULL when every row must have a status.
Should I use an enum or a lookup table?
Use an enum or CHECK constraint for a small, stable set that rarely changes. Prefer a lookup table if the options need descriptions, translations, custom ordering, activation dates, or frequent updates.
How do I remove an enum value?
There is no equally simple removal command across these databases. You may need to migrate rows to another value, replace a PostgreSQL type, alter a MySQL column, or revise a CHECK constraint. Confirm that no stored data or application code still uses the value before removing it.
Can I use numbers as enum labels?
You can define numeric-looking labels as strings in some database systems, but this can confuse their meaning with internal enum positions. Prefer clear text labels or a lookup table with a numeric key if the application needs numeric identifiers.
Related
- Excel Data Validation Disabled? Causes and Fixes
- Excel Data Cleaning Freelancer Jobs: Skills and Rates
- Daily Vehicle Checklist Template Excel: What to Include
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets COUNTIF Not Equal to TextYes. In Google Sheets, use =COUNTIF(A2:A20,"<>Apple") to count cells in A2:A20 that are not equal to the text…
- Can You Play Split Fiction Online? Co-op ExplainedYes. Split Fiction can be played online in two-player co-op, including with a friend on a different supported…
- Emergency Contact List Template for Excel: Set It Up SafelyAn emergency contact list template in Excel is a worksheet with one row per person or service and…
- Excel Text to Columns: How to Undo and Restore DataTo undo Text to Columns in Excel for Microsoft 365 on Windows, press Ctrl+Z immediately after running it;…
- Find Blank Cells in Excel and Fill Them With ZeroTo find blank cells in an Excel range and fill them with zero, select the range, choose Home…
- Conditional Formatting Excel Whole Row: A Practical GuideYes. To format a whole row in Excel based on one cell, select the rows’ data range, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.