Inline foreign keys need both table sizes

Foreign keys are two-table operations.

That sounds obvious until you look at how they appear in real migrations:

ALTER TABLE appointments
  ADD COLUMN worker_id int REFERENCES workers(id);

At a glance, this looks like an ADD COLUMN check on appointments. But the statement also creates a foreign key relationship to workers. PostgreSQL has to care about both tables. A migration safety report should too.

Why the referenced table changes the risk

Size-aware scoring exists because the same statement has a different blast radius on a tiny table and a huge one.

Adding a foreign key touches the child table and takes a lock on the referenced table. If appointments has 20,000 rows and workers has 20 million rows, the referenced side still changes the operational story. The migration can queue behind activity on the large table, block writes in places the author did not expect, and create a review risk that is easy to miss if the analyzer only scores the child table.

That is why inline foreign keys have to include both table names in their affected-table set.

Inline syntax hides the constraint

The explicit form is easier to reason about:

ALTER TABLE appointments
  ADD CONSTRAINT appointments_worker_id_fkey
  FOREIGN KEY (worker_id)
  REFERENCES workers(id);

Most reviewers would read that as a constraint operation. The inline version compresses the same idea into a column definition:

ALTER TABLE appointments
  ADD COLUMN worker_id int REFERENCES workers(id);

Postgres still creates the relationship. The analyzer has to see through the syntax and score the referenced table too.

The safer rollout

For existing tables, the safer foreign key path is usually:

ALTER TABLE appointments ADD COLUMN worker_id int;

ALTER TABLE appointments
  ADD CONSTRAINT appointments_worker_id_fkey
  FOREIGN KEY (worker_id)
  REFERENCES workers(id)
  NOT VALID;

ALTER TABLE appointments
  VALIDATE CONSTRAINT appointments_worker_id_fkey;

The split matters. NOT VALID adds the constraint for new rows without scanning all existing rows immediately. VALIDATE CONSTRAINT can then scan under a weaker lock that does not block normal reads and writes.

If the column needs to become NOT NULL, keep that as a separate contract step with a validated check constraint first.

What to review

When you see an inline REFERENCES clause, ask three questions:

1. How large is the child table?
2. How large is the referenced table?
3. Does the migration use NOT VALID, or is it doing the whole relationship in one statement?

The first question catches long scans. The second catches unexpected locking pressure. The third catches the common one-shot migration that should be split into an expand and validate sequence.

Inline foreign keys are convenient syntax. They are not a smaller operational event.