# MySQL ENUM vs CHECK Constraint: Which Value List Changes Copy the Table

> On MySQL 8.4, appending a value to the end of an `ENUM` of up to 255 values is a metadata change that takes no time, while inserting one in the middle, reordering, or removing one copies the whole table and holds back writes. Removing a value that rows still hold fails with error 1265. An enforced `CHECK` on a `VARCHAR` copies the table for every change to its list, appends included, and a lookup table needs no `ALTER TABLE` at all.

Source: https://schemity.com/blog/mysql-enum-vs-check-constraint/

On MySQL 8.4, an `ENUM` is the cheapest value list to extend and among the most expensive to edit any other way. Appending a value to the end of the list changes only the table definition and finished in 0.00 seconds on a million rows. Inserting a value in the middle, reordering the list or removing a value copies the whole table and holds back writes while it does, and removing a value that rows still hold fails outright. A `CHECK (status IN (...))` on a `VARCHAR` copies the table for every change to its list, appends included. So whether `ENUM` or `CHECK` is the better choice depends on how the list will change, not on how it looks today.

Everything below ran on MySQL 8.4.11 in a throwaway Docker container on a laptop. The table is `tasks`, with 1,000,000 rows and a `status ENUM('open','done','blocked') NOT NULL DEFAULT 'open'` column. 400,000 rows hold `open`, 599,964 hold `done` and 36 hold `blocked`. Each change ran on a fresh copy of the table, and the timings are there to compare kinds of change, not to predict your server.

## MySQL ENUM vs CHECK constraint: which is cheaper to change?

The `ENUM` is cheaper only for an append within its storage width (and a change of letter case): every other `ENUM` edit, and every edit to an enforced `CHECK` list, copies the table.

MySQL stores an `ENUM` value as a number, the position of the value in the list, and keeps the strings in the table definition. Appending a string leaves every stored number pointing at the same value, so there is nothing to rewrite. Anything that moves a value to a different position, or takes one away, changes what the stored numbers mean, so MySQL rebuilds the table to write them again. The [online DDL reference](https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html) says it in one line: "Adding members in the middle of the list causes renumbering of existing members, which requires a table copy."

| Change to `status` on 1,000,000 rows | What MySQL does | Time |
| --- | --- | --- |
| Append `'archived'` at the end | `INSTANT` | 0.00 s |
| Insert `'review'` between `'open'` and `'done'` | Copies the table (`INSTANT` and `INPLACE` refused, 1846) | 1.58 s |
| Reorder to `('done','open','blocked')` | Copies the table | 1.50 s |
| Remove `'archived'`, which no row holds | Copies the table | 1.53 s |
| Remove `'blocked'`, which 36 rows hold | Fails, `ERROR 1265 (01000): Data truncated for column 'status' at row 2` | - |
| Change the case of `'done'` to `'Done'` | `INSTANT`, and every row now reads `Done` | 0.00 s |
| Append a 256th value to a 255-value `ENUM` | Copies the table | 1.46 s |
| Append a 9th member to an 8-member `SET` | Copies the table | 1.39 s |
| Append a 10th member to a 9-member `SET` | `INSTANT` | 0.00 s |
| Add `'archived'` to `CHECK (status IN (...))` on a `VARCHAR(20)` | Copies the table (`INSTANT` and `INPLACE` refused, 1845) | 1.65 s |

Neither MySQL nor PostgreSQL can alter a check constraint's expression, so changing the list means `DROP CHECK` and `ADD CONSTRAINT ... CHECK`, and adding a check makes MySQL read every row to validate it. MySQL does that by copying the table, even when the new list only adds a value and no row could fail it. The `DROP CHECK` alone is instant; it is the `ADD` that costs. PostgreSQL can split the cost with `NOT VALID` and a later `VALIDATE CONSTRAINT`; MySQL has no equivalent. Its `NOT ENFORCED` check is added instantly, but it enforces nothing, and switching it to `ENFORCED` copies the table.

A copy is not only slow, it holds back writes. On the same table grown to 3,000,000 rows, inserting `'review'` in the middle of the list copied for 4.94 seconds. A single-row `INSERT` sent 0.7 seconds in waited 4.33 seconds, while a `SELECT` by primary key sent at the same moment came back at once. Appending `'archived'` to the same 3,000,000 rows took 0.00 seconds.

## Does adding a value to a MySQL ENUM copy the table?

Not if the value goes at the end and the list stays within the same storage width.

An `ENUM` takes 1 byte per row up to 255 values and 2 bytes from 256 up to its limit of 65,535; a `SET` takes 1, 2, 3, 4 or 8 bytes for its up to 64 members, per the [storage requirements table](https://dev.mysql.com/doc/refman/8.4/en/storage-requirements.html). When an append crosses one of those widths, every row has to be rewritten at the new width, so the append copies the table like any other change. That is why the 9th `SET` member copied and the 10th did not, and why the 256th `ENUM` value copied. Few lists ever reach 255 values, but 8 `SET` members is an easy boundary to cross.

To make the cost visible before production, name the algorithm in the migration:

```sql
ALTER TABLE tasks
  MODIFY status ENUM('open', 'done', 'blocked', 'archived') NOT NULL DEFAULT 'open',
  ALGORITHM=INSTANT;
-- Query OK, 0 rows affected (0.00 sec)

ALTER TABLE tasks
  MODIFY status ENUM('open', 'review', 'done', 'blocked') NOT NULL DEFAULT 'open',
  ALGORITHM=INSTANT;
-- ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported.
-- Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.
```

The second statement changes nothing and says why. Without the clause it would have copied the table, because MySQL picks the cheapest algorithm it can and does not name it; the only sign is `1000000 rows affected` instead of `0 rows affected`, after the copy has run. The same clause catches a migration generated from a model where someone inserted `'review'` where it reads well, rather than at the end.

Order matters for a second reason. MySQL [sorts an `ENUM` by position](https://dev.mysql.com/doc/refman/8.4/en/enum.html), not alphabetically, so `ORDER BY status` on this table returns `open`, `done`, `blocked`. Appending keeps that order stable; putting a new value anywhere but last in a sort means inserting it or reordering the list, and both copy the table.

## How do I remove a value from a MySQL ENUM?

Move every row off the value first, then remove it, and expect the removal to copy the table.

With strict mode on, the MySQL 8.4 default, removing a value that any row holds fails before anything changes: `ERROR 1265 (01000): Data truncated for column 'status' at row 2`. Without strict mode, the same statement succeeds with one warning per row, 36 here, and those rows now hold the empty string, which the [`ENUM` documentation](https://dev.mysql.com/doc/refman/8.4/en/enum.html) calls the error value, with index 0. `SELECT status + 0` shows it. Nothing tells the application that 36 tasks lost their status. So before the change:

```sql
SELECT COUNT(*) FROM tasks WHERE status = 'blocked';   -- 36
UPDATE tasks SET status = 'open' WHERE status = 'blocked';
ALTER TABLE tasks MODIFY status ENUM('open', 'done') NOT NULL DEFAULT 'open';
```

Renaming a value is the same three steps: append the new name (instant), move the rows with an `UPDATE`, remove the old name (a copy). On a large table the `UPDATE` is the expensive step, one transaction over every row that holds the value, so run it in batches and have the application accept both names while the rows move. Changing `'done'` to `'completed'` in place is refused `INSTANT` with 1846 and fails with 1265 on rows holding `'done'`. There is no shorter route, with one exception: a change of letter case only. Under a case-insensitive collation such as the default `utf8mb4_0900_ai_ci`, relabelling `'done'` as `'Done'` was instant and every row read back as `Done`. Under `utf8mb4_bin` or `utf8mb4_0900_as_cs` the same statement failed with 1265, because there `'done'` and `'Done'` are different values. The default follows the same rule: `DEFAULT 'Open'` on a list holding `'open'` is accepted under `_ci` and stored as `'open'`, and refused on a `status ENUM('open','done') COLLATE utf8mb4_bin` column with `ERROR 1067 (42000): Invalid default value for 'status'`.

If the copy is too expensive, the alternative is not to remove the value at all: leave it in the list, stop the application writing it, and remove it the next time the table is rebuilt for another reason.

## Which should you use: ENUM, CHECK or a lookup table?

Use an `ENUM` for a list that only grows at the end, a lookup table for a list that changes often or carries data of its own, and a `CHECK` when portability matters more than the cost of changing it.

| | `ENUM` | `VARCHAR` + `CHECK` | Lookup table + foreign key |
| --- | --- | --- | --- |
| Add a value | Instant at the end; a copy anywhere else | Copies the table | An `INSERT`, no `ALTER TABLE` |
| Remove a value rows still hold | Fails, 1265 (strict) | Fails, 3819 (`Check constraint 'c_status_check' is violated.`) | Fails, 1451 (`Cannot delete or update a parent row`) |
| Storage per row | 1 byte (up to 255 values) | The string itself | The string or a numeric key |
| Sort order | Position in the list | Collation order | By label after a join, or a sort column you add |
| Moves to PostgreSQL or SQL Server | Needs converting | As written | As written |

The removal row matters as much as the add row: every option refuses to orphan a row, but only the lookup table does it without an `ALTER TABLE` on the table that holds the rows. Its cost is a join to read the label and one more table in the diagram.

## How Schemity shows what an ENUM change costs

Schemity's impact analysis reports which MySQL `ENUM` edits copy the table and how many rows hold a value you removed, before the migration runs.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. After you [connect to MySQL](https://schemity.com/doc/connect-mysql/), an `ENUM` column's values appear in the field dialog's Values box, and editing them there becomes a planned `ALTER TABLE ... MODIFY` statement.

![The tasks table read from a local MySQL 8.4 database in Schemity: id INTEGER auto, status ENUM with the default open underlined, title VARCHAR(100) defaulting to an empty string, and created_at DATETIME defaulting to NOW()](https://schemity.com/images/blog/mysql-enum-vs-check-constraint/canvas-tasks-enum.webp)

[Impact analysis](https://schemity.com/doc/impact-analysis/) applies the rules measured above:

- Append a value and there is no finding, because MySQL changes only the definition. Append the 256th `ENUM` value or the 9th `SET` member and the table rewrite appears.
- Insert, reorder or remove a value and the findings include "Rewrites tasks to change status" with the row estimate, and a note that changing the type of status holds back writes to `tasks` while it reads the same estimated rows.
- Remove a value and a "Can fail on existing data" finding appears ("Loses data" on a server without strict mode), with "Count exactly" to count the rows that still hold it. The count runs only when you ask, as a read-only `SELECT COUNT(*)` that matches the removed values, so 36 `blocked` rows show up before MySQL reports them as error 1265.
- A change of letter case is matched the way the column's collation compares: no finding under `_ci`, a failing removal under `_bin` or `_cs`.

Appending `archived` after `open`, `done` and `blocked` in the Values box is the first case, and the Impact drawer stays empty:

![Schemity's field dialog for tasks.status: type ENUM, Values open, done, blocked and archived, charset utf8mb4, collation utf8mb4_0900_ai_ci, default open](https://schemity.com/images/blog/mysql-enum-vs-check-constraint/field-status-append-archived.webp)

![Schemity's Impact drawer after archived is appended to tasks.status on MySQL 8.4: Impact (0), No findings, because MySQL changes only the table definition](https://schemity.com/images/blog/mysql-enum-vs-check-constraint/impact-enum-append.webp)

Remove `blocked` instead and the same drawer shows three findings: the change can fail on existing data, it rewrites `tasks`, and it holds back writes while it reads the estimated ~997.6K rows, 42 MB on disk.

![Schemity's field dialog for tasks.status with blocked removed: Values open and done, collation utf8mb4_0900_ai_ci, default open](https://schemity.com/images/blog/mysql-enum-vs-check-constraint/field-status-remove-blocked.webp)

![Schemity's Impact drawer after blocked is removed from tasks.status on MySQL 8.4: Impact (3), Can fail on existing data with Changes the type of tasks.status, fails if a value does not fit, ~997.6K rows and a Count exactly button; Rewrites the table with Rewrites tasks to change status, ~997.6K rows, 42 MB on disk; and Holds back other statements with Changing the type of status holds back writes to tasks](https://schemity.com/images/blog/mysql-enum-vs-check-constraint/impact-enum-remove-blocked.webp)

![The Findings panel for the same change: the planned migration ALTER TABLE tasks MODIFY COLUMN status ENUM('open','done') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'open', with no ALGORITHM clause, one planned change, Lint 0 and the three impact findings](https://schemity.com/images/blog/mysql-enum-vs-check-constraint/planned-migration-remove-blocked.webp)

[Schema lint](https://schemity.com/doc/schema-lint/) adds one rule for the same column type: `mysql-enum-default-not-listed` reports an `ENUM` or `SET` default that is not one of its values, which MySQL refuses with error 1067, compared under the column's collation. That is how the diagram lets you see the impact of every change to a value list: the append and the insert look alike in a migration file, and only one of them copies the table.

## MySQL ENUM changes, in one list

Only an append within the same storage width, or a change of letter case under a `_ci` collation, is free; everything else rewrites the table or fails.

- Appending to an `ENUM` or `SET` is `INSTANT`, unless it crosses 255 `ENUM` values or a `SET` storage width (8, 16, 24 or 32 members).
- Inserting in the middle, reordering and removing a value copy the table and hold back writes until the copy ends.
- Removing a value that rows hold fails with 1265 in strict mode and silently empties those rows without it; move them first.
- Changing an enforced `CHECK` list copies the table every time, appends included; a lookup table changes with an `INSERT`.
- Add `ALGORITHM=INSTANT` to an append so MySQL refuses, rather than copies, if the change is not one, and set `lock_wait_timeout` so even an instant change cannot queue reads behind an open transaction.

The other MySQL `ALTER TABLE` changes that copy, from a `VARCHAR` crossing 255 bytes to a foreign key added with checks on, are timed in [MySQL ALTER TABLE: which changes lock the table](https://schemity.com/blog/mysql-alter-table-lock-instant-inplace-copy/), and what happens to the statements before a failed one is covered in [which failed migrations roll back](https://schemity.com/blog/transactional-ddl-postgres-mysql-cockroachdb/). The same choice on PostgreSQL, where enums are a type of their own, is compared in [Postgres enum vs check constraint vs lookup table](https://schemity.com/blog/postgres-enum-vs-check-constraint-vs-lookup-table/), and if you are [converting a MySQL schema to PostgreSQL](https://schemity.com/blog/mysql-to-postgresql-schema-conversion/), every `ENUM` needs that decision anyway.

## Frequently asked questions

### How do I add a value to a MySQL ENUM without copying the table?

Add it at the end of the list and name the algorithm: `ALTER TABLE tasks MODIFY status ENUM('open','done','blocked','archived') NOT NULL DEFAULT 'open', ALGORITHM=INSTANT`. MySQL then changes only the table definition. If the value has to go anywhere but the end, or the list grows past 255 values, MySQL refuses the `INSTANT` clause with error 1846 instead of copying the table, which tells you before production that the change is a copy. Even an instant change waits for open transactions on the table, so set `lock_wait_timeout` to a few seconds first.

### What happens to rows when you remove a value from a MySQL ENUM?

With strict mode on, which is the MySQL 8.4 default, the `ALTER TABLE` fails with error 1265, Data truncated for column, and nothing changes. Without strict mode it succeeds with a warning per row, and each row that held the removed value now holds the empty string, the error value with index 0. Count the rows first with `SELECT COUNT(*) FROM tasks WHERE status = 'blocked'` and move them to another value.

### Is a `CHECK` constraint better than an `ENUM` in MySQL?

It is more portable and its values are ordinary strings, but on MySQL 8.4 it is more expensive to change: replacing the `CHECK` copies the table even when you only add a value, where an `ENUM` append is instant. If the list changes often, a lookup table with a foreign key beats both, because adding a value is an `INSERT`.