warn-fk-lock-both-tables

What It Detects

Adding a foreign key locks BOTH the source and referenced table simultaneously.

Why It's Dangerous

ALTER TABLE ADD CONSTRAINT FOREIGN KEY acquires SHARE ROW EXCLUSIVE lock on both the table with the FK column AND the referenced table. This blocks writes to both tables simultaneously, doubling the blast radius.

Bad Example

ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id);

Good Example

SET lock_timeout = '3s';
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
RESET lock_timeout;
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

Configuration

Disable this rule:

# .migrationpilotrc.yml
rules:
  MP069: false

Or change its severity:

# .migrationpilotrc.yml
rules:
  MP069:
    severity: warning