A production table stores an enum attribute as ordinal integers and the team now needs to insert a new constant in the middle of the enum and move the column to string storage. How do you carry that out safely, and how would you detect damage if someone had already reordered the constants?

seniorshould knowfreq 30%

answer

Meaning lives in old source order — freeze it first
Snapshot GROUP BY distribution as baseline
Backfill with explicit CASE, never the new enum
Verify counts + business invariants before switching
Detect old damage via git diff + deploy-date discontinuity

basics

~20 s

Freeze the current ordinal-to-name mapping from the old source, add a new string column, backfill it by mapping each stored ordinal through that frozen table in SQL, switch the mapping to EnumType.STRING, then drop the integer column. To detect prior damage, reconstruct the mapping from git history and cross-check row counts and timelines against business expectations.

solid answer

~60 s

Treat it as a data migration keyed on a frozen ordinal-to-name table, because the meaning of the stored integers exists only in the old source code.

Recover the exact declaration order at the time each row was written — from the current source, and from git history if the enum ever changed.
Add a nullable status_str VARCHAR column.
Backfill in SQL with an explicit CASE status WHEN 0 THEN 'NEW' WHEN 1 THEN 'PAID' ... END, written from the frozen table, not generated by the new code.
Verify: no NULLs remain, per-value counts match the pre-migration GROUP BY status distribution, and spot-check rows against known business facts.
Deploy the mapping change to @Enumerated(EnumType.STRING) on the new column, add a CHECK constraint or native enum type, then drop the old column in a later release. Only after all that do you insert the new constant — by then position no longer matters.

To detect earlier damage, diff the enum's declaration order across releases and compare the resulting reinterpretation against invariants (a SHIPPED row must have a shipment; counts should not jump at a deploy boundary).

Treat it as a data migration keyed on a **frozen** ordinal-to-name table, because the meaning of the stored integers exists only in the old source code. 1. Recover the exact declaration order at the time each row was written — from the current source, and from git history if the enum ever changed. 2. Add a nullable `status_str VARCHAR` column. 3. Backfill in SQL with an explicit `CASE status WHEN 0 THEN 'NEW' WHEN 1 THEN 'PAID' ... END`, written from the frozen table, not generated by the new code. 4. Verify: no NULLs remain, per-value counts match the pre-migration `GROUP BY status` distribution, and spot-check rows against known business facts. 5. Deploy the mapping change to `@Enumerated(EnumType.STRING)` on the new column, add a `CHECK` constraint or native enum type, then drop the old column in a later release. Only after all that do you insert the new constant — by then position no longer matters. To detect earlier damage, diff the enum's declaration order across releases and compare the resulting reinterpretation against invariants (a `SHIPPED` row must have a shipment; counts should not jump at a deploy boundary).

code

sql · 14 lines

sql

-- baseline
SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY status;

-- backfill using the ordinal order frozen from the old source
UPDATE orders SET status_str = CASE status
  WHEN 0 THEN 'NEW'
  WHEN 1 THEN 'PAID'
  WHEN 2 THEN 'SHIPPED'
END
WHERE status_str IS NULL AND id BETWEEN ? AND ?;

-- verification
SELECT status_str, COUNT(*) FROM orders GROUP BY status_str;
SELECT COUNT(*) FROM orders WHERE status IS NOT NULL AND status_str IS NULL;

go deeper

for a junior

Recognise that the annotation change alone does not convert data and that a migration script must map old integers to names.

for a middle

Lay out the ordered steps — add column, backfill from the frozen mapping, verify, switch, drop — and explain why the frozen mapping matters.

for a senior

Own the operational detail: batching, overlap release and rollback, verification against a distribution snapshot and business invariants, and forensic detection of a past reorder.

for a principal

Frame the stored representation as a durable contract shared beyond the service, set the codebase default (STRING or explicit codes), and institute build-time guards so data meaning cannot drift with a source edit.

Why this is a migration, not a refactor

With EnumType.ORDINAL, the integer in the column has no meaning of its own — its meaning lives in the source order of the enum at the moment the row was written. Change the class and the data silently means something else. So any move away from ordinals must first pin down that external meaning, then rewrite the data while the old meaning still holds.

The fatal shortcut is to backfill with the new code:

sql

-- WRONG if the enum has been edited: uses today's order
UPDATE orders SET status_str = <mapped via new enum>;

If someone already inserted a constant, this cements the corruption. The backfill must be written from the frozen mapping.

Step by step

1. Reconstruct the frozen table. Read the enum as it exists now, and check git log -p for the file. If the declaration list never changed, today's order is the frozen order. If it did change, you need a per-period mapping and a way to tell which rows fall in which period — usually a created_at column against deployment dates. Write the table down explicitly in the migration script; do not compute it.

2. Snapshot the distribution. SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY status; before touching anything. This is your verification baseline and your evidence of what existed.

3. Add the new column, nullable. Deploy this alone; nothing reads it yet.

4. Backfill explicitly, in batches.

sql

UPDATE orders SET status_str = CASE status
  WHEN 0 THEN 'NEW'
  WHEN 1 THEN 'PAID'
  WHEN 2 THEN 'SHIPPED'
END
WHERE status_str IS NULL;

Batch by primary key range on a large table so you do not hold one enormous transaction. Handle NULLs and any out-of-range integers deliberately — an unmapped value is a finding, not something to default away.

5. Verify before switching. Assert zero NULLs where the source was non-NULL, and that per-name counts equal the snapshot's per-ordinal counts. Spot-check rows against independent facts: every SHIPPED order should have a shipment record; no NEW order should have a settled payment.

6. Switch the mapping. Change the attribute to @Enumerated(EnumType.STRING) @Column(name = "status_str") and deploy. Keeping both columns during the overlap means a rollback does not lose anything. If writes must continue during the overlap, write both columns for one release.

7. Harden and clean up. Add a CHECK (status_str IN (...)) constraint or, on PostgreSQL with Hibernate 6.2+, a native enum type via @JdbcTypeCode(SqlTypes.NAMED_ENUM). Drop the integer column in a later release, once you are confident nothing reads it.

8. Only now insert the new constant anywhere in the declaration. With string storage, position is irrelevant.

Detecting damage that already happened

If the enum was reordered in the past under ORDINAL storage, nothing failed and nothing was logged — you must go looking:

Diff the declaration order across releases (git log -p on the enum file) and note the deploy date of each change.
Look for a discontinuity in the distribution around that date: SELECT date_trunc('day', created_at), status, count(*) ... GROUP BY 1,2. A status that suddenly appears or vanishes at a deploy boundary is the signature of a shifted mapping.
Cross-check invariants against other tables: shipped orders without shipments, paid orders without payments, cancelled orders with subsequent activity.
Compare against external evidence — emails sent, events published, exports delivered — anything that recorded the status independently of this column.

Remediation is a targeted UPDATE bounded by the affected time window, using the old mapping for rows written before the deploy and the new one after. Do it in the same migration as the string conversion so there is a single, reviewable source of truth.

Prevention

Default to EnumType.STRING; where ordinals must stay for space reasons, add a test asserting each constant's ordinal so a reorder fails the build, and document the enum as append-only. An explicit code field converted on the boundary is the strongest option: independent of both order and Java names.

## Why this is a migration, not a refactor With `EnumType.ORDINAL`, the integer in the column has no meaning of its own — its meaning lives in the *source order of the enum at the moment the row was written*. Change the class and the data silently means something else. So any move away from ordinals must first pin down that external meaning, then rewrite the data while the old meaning still holds. The fatal shortcut is to backfill with the new code: ```sql -- WRONG if the enum has been edited: uses today's order UPDATE orders SET status_str = <mapped via new enum>; ``` If someone already inserted a constant, this cements the corruption. The backfill must be written from the **frozen** mapping. ## Step by step **1. Reconstruct the frozen table.** Read the enum as it exists now, and check `git log -p` for the file. If the declaration list never changed, today's order is the frozen order. If it did change, you need a per-period mapping and a way to tell which rows fall in which period — usually a `created_at` column against deployment dates. Write the table down explicitly in the migration script; do not compute it. **2. Snapshot the distribution.** `SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY status;` before touching anything. This is your verification baseline and your evidence of what existed. **3. Add the new column, nullable.** Deploy this alone; nothing reads it yet. **4. Backfill explicitly, in batches.** ```sql UPDATE orders SET status_str = CASE status WHEN 0 THEN 'NEW' WHEN 1 THEN 'PAID' WHEN 2 THEN 'SHIPPED' END WHERE status_str IS NULL; ``` Batch by primary key range on a large table so you do not hold one enormous transaction. Handle NULLs and any out-of-range integers deliberately — an unmapped value is a finding, not something to default away. **5. Verify before switching.** Assert zero NULLs where the source was non-NULL, and that per-name counts equal the snapshot's per-ordinal counts. Spot-check rows against independent facts: every `SHIPPED` order should have a shipment record; no `NEW` order should have a settled payment. **6. Switch the mapping.** Change the attribute to `@Enumerated(EnumType.STRING) @Column(name = "status_str")` and deploy. Keeping both columns during the overlap means a rollback does not lose anything. If writes must continue during the overlap, write both columns for one release. **7. Harden and clean up.** Add a `CHECK (status_str IN (...))` constraint or, on PostgreSQL with Hibernate 6.2+, a native enum type via `@JdbcTypeCode(SqlTypes.NAMED_ENUM)`. Drop the integer column in a later release, once you are confident nothing reads it. **8. Only now insert the new constant** anywhere in the declaration. With string storage, position is irrelevant. ## Detecting damage that already happened If the enum was reordered in the past under ORDINAL storage, nothing failed and nothing was logged — you must go looking: - **Diff the declaration order across releases** (`git log -p` on the enum file) and note the deploy date of each change. - **Look for a discontinuity in the distribution** around that date: `SELECT date_trunc('day', created_at), status, count(*) ... GROUP BY 1,2`. A status that suddenly appears or vanishes at a deploy boundary is the signature of a shifted mapping. - **Cross-check invariants** against other tables: shipped orders without shipments, paid orders without payments, cancelled orders with subsequent activity. - **Compare against external evidence** — emails sent, events published, exports delivered — anything that recorded the status independently of this column. Remediation is a targeted `UPDATE` bounded by the affected time window, using the old mapping for rows written before the deploy and the new one after. Do it in the same migration as the string conversion so there is a single, reviewable source of truth. ## Prevention Default to `EnumType.STRING`; where ordinals must stay for space reasons, add a test asserting each constant's ordinal so a reorder fails the build, and document the enum as append-only. An explicit code field converted on the boundary is the strongest option: independent of both order and Java names.

Why not simply change the annotation to EnumType.STRING and let Hibernate rewrite the values?
Because Hibernate does not rewrite anything. The annotation only changes how new values are bound and how the column is read; the existing column still holds integers, so the very first read fails to convert or, if you also renamed the column, returns nothing. The data must be converted explicitly by a migration before the mapping switches over.
How do you keep the application writable during the conversion?
Run an overlap release: keep the integer column, add the string column, and have the application write both for one deployment while reads still come from the old column. Backfill historic rows in batches, verify, then flip reads to the string column in the next release and stop writing the integer. Dropping the old column happens a release later, which keeps rollback cheap at every step.
What guard prevents this problem from recurring while a column is still ordinal-mapped?
A unit test that asserts the ordinal of every constant and the total count, so any insertion or reorder fails the build with an explicit message pointing at the persisted data. Combine it with a comment marking the enum append-only, and treat the test as documentation of a data contract rather than a trivial assertion.

saying these in an interview costs you the question

Backfilling with the new enum order instead of the frozen historical order
Believing changing the annotation migrates existing data
Doing the whole conversion in one release with no overlap or rollback path
Skipping the pre-migration distribution snapshot, leaving no way to verify
Assuming a past reorder would have thrown an error somewhere

A production table stores an enum attribute as ordinal integers and the team now needs to insert a new constant in the middle of the enum and move the column to string storage. How do you carry that out safely, and how would you detect damage if someone had already reordered the constants?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Why this is a migration, not a refactor

Step by step

Detecting damage that already happened

Prevention

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions3 questions

▾related topics3 topics

Why this is a migration, not a refactor

Step by step

Detecting damage that already happened

Prevention

answer

go deeper

context