For a fixed set of values like an order status, the safe default in PostgreSQL is a text column with an IN check constraint: adding or removing an allowed value is a constraint swap instead of a type rewrite. Reserve the native enum type for a set you are confident will never change, and reach for a lookup table only when the value carries data of its own.
That ordering surprises people, because the native enum looks like the purpose-built answer. It is a real type, the engine rejects any string outside the set at parse time, and the column is compact. Then somebody asks to add on_hold to the order lifecycle, and you find out that the tidy-looking choice you made in year one has turned a one-line product change into a coordinated migration.
What actually goes wrong with native Postgres enums
The trouble is entirely in the second half of the value set’s life. Adding a value is cheap - it is a catalogue operation with no table rewrite. Removing one is not, because PostgreSQL has no DROP VALUE. Retiring a value means creating a new type with the values you want, casting every column across with a USING clause, dropping the old type and renaming the new one into place. That column rewrite runs under an ACCESS EXCLUSIVE lock.
The team at Close hit this and wrote up why they chose check constraints over native enums, and their description of the lock is the part worth keeping: “This lock is the most restrictive of all locks in PostgreSQL: your transaction is the only transaction that can access that table while it exists.” On a table every tenant is reading and writing right now, that is not a migration you run on a Tuesday afternoon. Their alternative - a check constraint added NOT VALID and validated afterwards - takes a SHARE UPDATE EXCLUSIVE lock instead, which permits concurrent updates while the validation scan runs.
The transactional restrictions bite too. The PostgreSQL documentation for ALTER TYPE still states plainly: “If ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) is executed inside a transaction block, the new value cannot be used until after the transaction has been committed.” PostgreSQL 12 relaxed the rule enough to let the statement run inside a transaction block at all, and PostgreSQL 17 allows a newly added value to be used in the same transaction when the type was created there - but for an existing type on an existing table, adding a value and then using it is still two deploys. A detailed account of dropping enums from a 120-developer monorepo puts a number on what that costs at velocity: roughly 6,000 pull requests in eleven months, with ALTER TYPE sitting in the path of any change to a value set. Their summary of the switch is the honest measure of the difference - “adding a new value stopped being a migration at all.”
Crunchy Data’s Craig Kerstiens reaches the same recommendation from the design side rather than the operations side, noting that a check constraint can express rules an enum cannot - like requiring a tracking_id to be NOT NULL when status is shipped - and advising anyone considering an enum to test drive the check constraint first.
Should I use a Postgres enum, a check constraint, or a lookup table?
Decide by asking how the set will change, not by asking which one is most “correct”:
Native enum type | IN check constraint | Lookup table | |
|---|---|---|---|
| Add a value | Catalogue change, no rewrite, but unusable until commit | Drop and re-add the constraint, or add NOT VALID then validate | INSERT one row, no migration |
| Remove a value | No DROP VALUE: recreate the type, rewrite the column | Swap the constraint definition | DELETE the row, blocked by the foreign key while in use |
| Lock taken on change | ACCESS EXCLUSIVE on every table using the type | SHARE UPDATE EXCLUSIVE when validated separately | Row locks only |
| Extra attributes per value | None | None | Any columns you want |
| Enforced by the database | Yes | Yes | Yes, via a foreign key |
| Storage per row | Fixed-width internal value | The text itself | Foreign key column plus a join to read the label |
| Visible in a typical ERD | Type name only, values elsewhere | Usually dropped on import | Yes, as a table and a 1:N relationship |
| Best for | A closed set that will not change | The default for application status and type columns | Values that carry data or that non-developers maintain |
The rule of thumb that falls out of that table: if the set of values is part of the deployed application’s vocabulary, use a check constraint. If a value is a row of domain data - a country, a currency, a subscription plan with a price and a display order - it wants a lookup table and a foreign key, because you will inevitably need a second column about it. And if you genuinely have a closed set with no migration velocity, the native enum is fine; it is just a much narrower case than its popularity suggests.
One variant falls outside the table entirely: a column that holds several of the values at once rather than one. On PostgreSQL that is usually written as an array, and the decision changes shape, because an array column stores a list the row owns while a junction table stores references the database can enforce.
There is a fourth situation worth naming, because it is the one people mistake for an enum: a value set that is derived from the codebase rather than chosen by a person. Permission strings are the classic case, and the reason permissions in a multi-tenant RBAC schema are generated from the routing table rather than typed into an enum is that the source of truth for those values is the router, not the database.
Which option do Django, Rails, Prisma and TypeORM pick for you?
In practice most teams never make this decision directly - the ORM makes it. It is worth noticing that on PostgreSQL, none of the popular stacks defaults to the check constraint.
Django’s choices option validates only in application code. full_clean() and model forms reject a value outside the set, but the generated column is a plain varchar with no constraint, so a raw UPDATE, a data migration script or a second service writing to the same database can insert anything. Getting the database to enforce the set takes an explicit entry in Meta.constraints - a CheckConstraint with Q(status__in=[...]) - which Django has supported since 2.2 but has never generated for you.
Rails tells the same story with a twist: the enum macro maps labels to integers by default, so the column holds 0, 1, 2 with no constraint and no legibility - a psql session cannot tell you what 2 means without reading the model. Rails 7 added create_enum and t.enum for native PostgreSQL enum types, which buys database enforcement at the price of exactly the removal-and-lock problem described above. add_check_constraint has been in the migration DSL since Rails 6.1, but nothing in the framework steers you toward it.
TypeORM is the near miss, because it proves the check constraint works and then declines to use it on Postgres. Its simple-enum column type emulates an enum as a varchar with an IN check constraint - but only on databases that lack a native enum type, meaning SQLite and SQL Server. On PostgreSQL, both enum and simple-enum resolve to a native enum type, so a Postgres project gets the type-rewrite story anyway. TypeORM does ship an explicit @Check() decorator, so like Django’s Meta.constraints the rule can at least live in the entity file instead of a raw SQL migration - but nothing generates it from the value set for you.
Prisma steers hardest of all: an enum block in the Prisma schema maps directly to a native PostgreSQL enum type, and a check constraint cannot be declared in the Prisma schema at all. One added with raw SQL in a migration survives in the database and Prisma will respect it, but the schema file will never mention it. The default path for a Prisma team is therefore the option this comparison ranks last for any set that changes.
The pattern across all four is consistent: the framework either keeps the rule in application memory, where the database cannot enforce it, or reaches for the native type, which is the hardest to change later. The check constraint loses by default not because it is worse but because it is invisible to the tooling - the ORM’s schema file does not model it, and most diagram tools drop it on import. That is a legibility gap, not an argument against the constraint, and it is the same gap the next section is about.
How each choice reads in the ERD
Whichever of the three you pick, the question a reader will actually ask about the column is the same: what are the valid values? A lookup table answers it structurally, because the values are rows and the relationship is drawn. The other two answer it only if the diagram tool bothered to carry the constraint - and most do not. You get status TEXT and a shrug, which is the same failure as an ERD that hides the domain model behind a column list.
Schemity reads check constraints and composite unique constraints out of the live schema and renders them where the field is. Because it reads the database rather than the ORM’s files, this works even when the constraint was added with raw SQL that your Prisma schema or Rails model has no way to record - the diagram shows the rule your framework cannot. A field covered by an IN check constraint is underlined on the canvas - a deliberate signal that this is an enum-like field, a value object in domain terms, and that a defined set stands behind it.
Press Ctrl/Cmd + O on that field and the allowed values list opens without going through the edit form, so answering “can an order be on_hold?” is a keystroke rather than an information_schema query.
The same values populate the default-value picker, so setting a column’s default means choosing from the constrained set instead of retyping a string that has to match.
The entity footer counts fields, indexes, check constraints and unique constraints, so a table carrying rules is distinguishable from a bag of columns at a glance.
The lookup-table option needs no special support, which is rather the point: it is an ordinary 1:N relationship drawn with crow’s foot notation between the referencing table and the value table. What matters there is that the relationship is created as a real foreign key with a naming convention applied, not as a decorative line. If the lookup ends up being many-to-many - a tenant enabling a subset of plans, say - the junction table is created for you rather than hand-built in six steps.
The native enum type is the one case where the diagram genuinely cannot show you much, in any tool. The values live in pg_enum, attached to a type rather than to a column, which is exactly why the type name on the field is all a reverse-engineered diagram has to work with. That is a real argument against enums that gets left out of the usual comparison: the choice that is hardest to change later is also the one that documents itself least.
Changing the value set later is a migration, and the diff should say so
The reason the choice matters at all is that value sets change, so what you want from a tool is for the change to be visible before it runs. When you reverse engineer a PostgreSQL, MySQL, SQL Server or SQLite schema, the check constraints come across with the tables, so the diagram starts out describing the rules production actually enforces rather than the ones from the design document. Edit the allowed values on a field and Schemity generates the migration SQL diff for you to read - you decide whether to apply it, and only then does anything touch the database. Dropping cancelled from an order status set is precisely the change that deserves a human reading the statement first, because the rows already holding that value do not disappear just because the constraint stopped allowing it.
Because the diagram is a plain JSON file in your workspace folder, the same edit is also a line in a pull request diff, which is the second place a reviewer can catch it. A change to a value set is a change to the domain vocabulary, and keeping the diagram in Git is what makes it reviewable as one. This is the same loop as not hand-translating between SQL and your ERD: the model is edited once, and both the database change and the documentation change fall out of it.
One consequence to plan for regardless of which storage you chose: retiring a value is a data migration before it is a schema migration. Existing rows must be moved to a surviving value, and if the column is part of a uniqueness rule the retirement can change what counts as a duplicate - the same inversion that soft deletion causes for unique constraints. The constraint change is the last step, not the first.
The value set is part of the model, so store it where the model can show it
An ERD is only a single source of truth if it carries the facts the schema carries. “This column is a string” is not one of those facts when the reality is “this column is one of five named states, and three of them are terminal.” The set of allowed values is domain vocabulary; it belongs in the diagram next to the column, not in a spreadsheet, and certainly not in the memory of whoever wrote the migration.
That is the strongest practical reason to prefer check constraints or lookup tables over native enum types, ahead of the locking argument. A check constraint is legible - it can be read off the field, marked on the canvas, and diffed in a pull request. A lookup table is legible by construction, since it is a table. A native enum hides its content in a catalogue that most diagram tools never open, which is a lot to pay for a few bytes per row. The column that got none of the three - a bare status string with nothing restricting its values - is the one worth catching automatically, and it is the kind of finding a linter that reads the schema rather than the migration exists for, since the absence of a constraint appears in no migration file at all. Whichever way you go, make the choice deliberate and make it visible, so the next person can find the column and its rules without writing a query - and so the data dictionary lives in the ERD rather than in a document nobody updated.
Frequently asked questions
Can you remove a value from a Postgres enum type?
Not directly. PostgreSQL has no DROP VALUE for enum types, so retiring a value means creating a replacement type, casting every column across with a USING clause, dropping the old type and renaming the new one. That column rewrite takes an ACCESS EXCLUSIVE lock, which blocks reads and writes on the table for the duration.
Are check constraints slower than native enums?
A native enum stores a fixed-width internal value while a check constraint usually guards a text column, so the enum is more compact on disk. In exchange, the check constraint is validated with a SHARE UPDATE EXCLUSIVE lock that allows concurrent reads and writes when you add it with NOT VALID and validate afterwards. For most application tables the operational difference dominates the storage difference.
When is a lookup table the right choice instead?
When the value needs attributes of its own - a display label, a sort order, an active flag, a per-tenant subset - or when non-developers must add values without a deploy. The cost is a join for every read and a foreign key on every referencing table, so use it for domain data that grows, not for a closed set of three states.
Do Django choices or Rails enums create a database constraint?
No. Django's choices are validated in application code only - the column itself is a plain varchar that accepts any value from raw SQL. Rails' enum macro maps labels to bare integers with no constraint, and Rails 7's native enum support creates a PostgreSQL enum type instead. In both frameworks a check constraint must be added explicitly, via Meta.constraints in Django or add_check_constraint in a Rails migration. Prisma cannot declare check constraints in its schema at all, and TypeORM's simple-enum only emulates an enum with a check constraint on SQLite and SQL Server - on PostgreSQL it creates a native enum type.