An enum (short for “enumeration”) in a database is a data type that restricts a column to a predefined set of allowed values. When you define a column as an enum, you list the permitted values in order, and the database stores each row’s value as an integer index pointing to that list, while displaying the corresponding string. This gives you a compact way to enforce a fixed set of choices — for example, a status column that can only contain 'active', 'inactive', or 'pending'.
Where the Term Comes From
The word enumeration comes from Latin enumerare, meaning “to count out” or “to list one by one.” In programming, an enumerated type (enum) has existed since early languages like Pascal and C, where it lets you define a named set of constants. Databases adopted the concept later: MySQL introduced the ENUM type in the 1990s, and PostgreSQL has a similar CREATE TYPE ... AS ENUM feature. However, it is not a standard SQL type — the SQL standard (ISO/IEC 9075) does not define an enum data type. Instead, standard SQL offers CHECK constraints and REFERENCES to foreign keys as the portable way to restrict values. So, in the database world, “enum” is a widely used but nonstandard term, specific to certain database systems.
How Enum Works in Different Databases
Two major database systems support enum types, but they work slightly differently:
- MySQL / MariaDB: You define an enum directly in the column definition, like
status ENUM('active','inactive','pending'). MySQL stores the value as a 1- or 2-byte integer (the index of the list, starting at 1) and maps it back to the string on retrieval. The order you list the values matters because it determines the sort order and the index number. - PostgreSQL: You create a standalone enum type first with
CREATE TYPE status_type AS ENUM ('active','inactive','pending');then use it in a column:status status_type. PostgreSQL stores the enum as a 4-byte internal identifier, and the type can be reused across multiple tables. Adding a new value to a PostgreSQL enum requires anALTER TYPEcommand.
Other databases like SQL Server, Oracle, and SQLite do not have a native enum type. In those systems, you simulate an enum with a CHECK constraint or a lookup table.
When to Use an Enum
Enums are best suited for columns where the set of allowed values is small, stable, and unlikely to change. Common examples include:
- Order status:
'pending','shipped','delivered','cancelled' - User roles:
'admin','editor','viewer' - Priority levels:
'low','medium','high' - Days of the week, months, or other fixed code lists
Use an enum when you want the database itself to enforce the allowed values, and when you rarely need to add new options. If the list might change frequently, a lookup table with a foreign key is more flexible because you can insert new rows without altering the table structure.
Pros and Cons of Using Enums
Like any design choice, enums have trade-offs. Here are the main advantages and disadvantages to consider:
Advantages
- Data integrity: The database rejects any value not in the list, so you don’t need extra application-level validation.
- Storage efficiency: MySQL stores enums as integers, which can be more compact than storing full strings.
- Readability: Queries return the string value, so the data is human-readable without joins.
Disadvantages
- Inflexibility: Adding a new value requires an
ALTER TABLE(MySQL) orALTER TYPE(PostgreSQL), which can lock the table and cause downtime on large datasets. - Nonstandard: Because it’s not part of the SQL standard, moving to another database often requires rewriting the schema.
- Sort order oddities: In MySQL, enums sort by their index number, not alphabetically. For example,
'medium'might sort before'high'if you defined the list in that order. - Hidden integer values: If you ever export the data, you might see numbers instead of strings, which can confuse non-technical users.
How to Choose Between Enum, Check Constraint, and Lookup Table
When you need to restrict values, you have three main options. Here’s a quick decision guide:
- Use an enum if the value set is truly fixed (e.g.,
'male'/'female'or weekdays) and you are on MySQL or PostgreSQL. - Use a CHECK constraint if you are on a database without enums (SQL Server, Oracle, SQLite) or if you want to keep the schema portable. For example:
CONSTRAINT chk_status CHECK (status IN ('active','inactive')). - Use a lookup table with a foreign key if the list might grow, if you need extra attributes (like a display order or description), or if you want to allow users to manage the list via the application.
In practice, many developers prefer lookup tables for anything beyond a handful of static values, because they offer the most flexibility and are standard across all databases.
Common Problems and Misconceptions
One common misconception is that enums are faster than joins to a lookup table. In reality, the performance difference is negligible for most applications; the real cost is in maintenance. Another issue is that in MySQL, if you try to insert an invalid value, the database may silently truncate it to an empty string (unless strict SQL mode is on). This can lead to data corruption if you don’t enable strict mode. Also, reordering enum values in MySQL is not straightforward — you must drop and re-add the column, which can be risky. Finally, some people assume enums are part of standard SQL, but they are not; this becomes a problem when migrating databases.
Frequently Asked Questions
Can I add a new value to an enum later?
Yes, but it requires an ALTER TABLE in MySQL (e.g., ALTER TABLE orders MODIFY status ENUM('active','inactive','pending','refunded')) or an ALTER TYPE in PostgreSQL (e.g., ALTER TYPE status_type ADD VALUE 'refunded'). In MySQL, this can lock the table, so it’s best to do it during low-traffic periods. In PostgreSQL, adding a value is less disruptive but still requires a lock.
What happens if I insert a value not in the enum?
In MySQL, with strict SQL mode enabled, the insert fails with an error. Without strict mode, MySQL inserts an empty string (or the special error value) and issues a warning. In PostgreSQL, the insert always fails with an invalid input syntax error. Always enable strict mode in MySQL to avoid silent data corruption.
Is enum faster than a lookup table?
For very small tables, the difference is negligible. Enums may save a tiny amount of storage and avoid a join, but a lookup table with an integer foreign key and an index is often just as fast. The main trade-off is flexibility, not speed.
Can I use an enum in SQL Server or Oracle?
No, those databases do not have a native enum type. You can simulate it with a CHECK constraint or a lookup table. For example, in SQL Server: status VARCHAR(10) CHECK (status IN ('active','inactive')).
How do I see the enum values in a table?
In MySQL, you can use SHOW COLUMNS FROM table_name LIKE 'status' to see the enum definition. In PostgreSQL, query the pg_enum system catalog or use \d table_name in psql to see the type details.
Should I use enum for boolean-like values?
For a simple yes/no or true/false, a native boolean type (if available) is usually better. Enums are overkill for two values and can complicate queries. Only use an enum when you have three or more distinct, stable options.
Related
- How to Add Speaker Notes in PowerPoint
- Balance of Trade Data: What It Is and How to Read It
- F.08 Export to Excel: How to Do It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Move the Quick Access Toolbar in Excel (Windows)In Excel for Microsoft 365 on Windows, click the Quick Access Toolbar’s small customization arrow and choose Show…
- Excel Heat Map by City: Steps, Data, and Map OptionsFor a city-by-city heat map in Excel, put each city and its numeric value in a table, select…
- Merge Cells in Excel: How to Keep All Your DataNo: in Microsoft Excel, merging cells keeps only the value in the upper-left cell and discards the contents…
- Goal Seek in Excel: A Practical SampleTo use Goal Seek in Excel, create a formula, then choose Data > What-If Analysis > Goal Seek.…
- Hanging Indent in Word: A Step-by-Step GuideTo create a hanging indent in Microsoft Word, select the paragraph or paragraphs, open the Paragraph settings, and…
- Excel Budget Conditional Formatting: Setup and RulesYes. To show which items are under budget in Excel, select the actual-spending cells and create a conditional-formatting…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.