Change a schema while old data still exists

First, build a mental model

A migration is a controlled transition between supported states. Expand what the database accepts, move data and consumers, then contract only after compatibility is proven.

1. Adding a nullable column can preserve old records while new code begins writing it. A future NOT NULL requirement needs an explicit valid-data transition.
2. A unique index on company plus external reference can enforce uniqueness within each company. The partial predicate here intentionally ignores missing references.
3. Check real duplicate/null distributions before tightening constraints. Never invent a harmless-looking default that changes the meaning of historical records.
4. Review forward migrations and replay/upgrade paths. SQLite can run this example, but PostgreSQL locking, concurrent index options and ALTER details require separate validation.

ALTER TABLE orders ADD COLUMN external_ref TEXT;
CREATE UNIQUE INDEX orders_company_reference
  ON orders(company_id, external_ref)
  WHERE external_ref IS NOT NULL;
UPDATE orders SET external_ref = 'web-101' WHERE id = 101;
SELECT id, company_id, external_ref FROM orders ORDER BY id;

1. Expand

   Old rows gain a nullable column without fabricating reference IDs.

2. Constrain known values

   A repeated non-null reference within one company conflicts; missing references remain allowed.

3. Move consumers

   New code can write references while migration/backfill work explicitly handles retained rows.