Constraints Get In The Way
Foreign keys enforce that a child row references a valid parent. They protect integrity, but during migrations they create ordering, locking, and tooling problems that need careful handling.
Where They Bite
- Insert ordering: when copying data, parents must load before children, or the constraint rejects the child.
- Validation locks: adding a foreign key makes the engine scan and lock to check existing rows reference valid parents.
- Tool incompatibility: shadow table tools struggle because a swapped table breaks foreign keys pointing at its old name.
These mean a constraint that is harmless in steady state becomes an obstacle mid migration.
Practical Tactics
- Load tables in dependency order, parents first, so references always resolve.
- Add the constraint as not valid first, then validate separately to avoid one long blocking scan.
- For shadow table migrations, temporarily drop and recreate dependent foreign keys around the swap, or migrate the referencing tables together.
The guiding idea is to defer enforcement during the heavy data movement and restore full integrity once the data is in place.
Key idea
Foreign keys complicate migrations through insert ordering, validation locks, and tool conflicts, so teams load in dependency order, add constraints as not valid, and restore enforcement after the data lands.