A CHECK Rewrite Erased Another Branch's Value

The short version

Managing a PostgreSQL CHECK constraint by DROP CONSTRAINT and then rewriting it with ADD CONSTRAINT is only “additive” with respect to the values the author happens to know about.

A value another branch added earlier falls out of the constraint the moment it is rewritten, even though nobody meant to remove it. And the migration itself succeeds without error. The comment “additive, idempotent, non-destructive” only holds inside that one branch.

What it looks like

The share_links table has a kind column restricting the type of public link — invoice, quote, application and so on — with CHECK (kind in (...)). Every time a new type was added, the migration was written like this.

alter table public.share_links
  drop constraint if exists share_links_kind_check;
alter table public.share_links
  add constraint share_links_kind_check
  check (kind in ('invoice','quote','application','report'));  -- 0040

  check (kind in ('invoice','quote','application','report','file'));  -- 0044

  check (kind in ('invoice','quote','application','report','file','bundle'));  -- 0045

  check (kind in ('invoice','quote','application','report','file','bundle','group_invoice'));  -- 0054

0040, 0044, 0045 and 0054 all carry “additive, idempotent, non-destructive” in their opening comment. And seen from this trunk alone, the values do grow by exactly one each time, with no effect on existing rows.

Meanwhile a separate long-lived branch implemented receipts, and its migration adding receipt to kind went to production on 2026-06-13. The trunk was never merged with that branch, so nobody writing 0040 onward knew receipt existed.

As deploys from the trunk moved forward, creating a share_link with kind='receipt' began failing on the CHECK. Issuing a receipt was impossible in production. Nowhere in the migration history is there an error. The constraint had simply been rewritten “correctly”, over and over.

Why

Managing allowed values with DROP CONSTRAINT plus ADD CONSTRAINT behaves like this.

whoever wrote 0040 knew kind = {invoice, quote, application, report}
  → rewrites CHECK with those four (correct at that moment)

whoever wrote 0044 knew kind = {invoice, quote, application, report, file}
  → rewrites CHECK with those five (still correct at that moment)

……

the person who added receipt never took part in this chain of rewrites
  → nobody on the trunk knows receipt exists, and the rewrites continue
  → each rewrite leaves receipt outside the constraint as an "unknown" value

Each migration really is additive with respect to the set it knows. But because the implementation rewrites the whole IN list every time, any value not enumerated there leaves the constraint, regardless of intent. With branches diverging while both ship to production, this happens even when the unenumerated value is one that is actively in use.

Checking it

To find out which values production allows right now, read the constraint definition actually applied, rather than scanning migration files by descending number.

SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'public.share_links'::regclass
  AND conname = 'share_links_kind_check';

The IN list that comes back is the complete set of values production accepts. Walking migration files backwards from the newest number, collecting occurrences of kind, will never surface a value only another branch added.

Fixing it

Restore the lost value by rewriting the constraint idempotently with the union of every kind up to that point.

-- 0055_share_links_receipt_restore.sql
alter table public.share_links drop constraint if exists share_links_kind_check;
alter table public.share_links
  add constraint share_links_kind_check
  check (kind in ('invoice','quote','application','report','file','bundle','group_invoice','receipt'));

We do not rewind the number to 0031. The trunk is already at 0054, and assigning a new 0031 now breaks chronological order. The fix is to write out, at the latest number, every value that should exist by that point.

Making it not recur

The sources behind this article say nothing about how the design changed afterwards. Only the structural point can be made here.

“Read migration files backwards from the newest number” and “read the constraint actually applied in production” agree only when there is exactly one branch. Where diverged branches can each add values to the same column independently, following the file history gives you no way to learn that a value your branch has never seen exists at all.

Reading the real constraint with pg_get_constraintdef was used here as the step before restoring the value. But the same check belongs to the side that adds a value — the next person who wants to put something new in kind. Write the IN list after looking at what production holds now, rather than at the newest migration number, and at least the “removed it without knowing” half of this accident stops happening.