Postgres add foreign key with Schema Diff Studio
A blog schema is loaded before and after its relations were tightened: posts.author_id gets a foreign key it never had, comments switches from the default ON DELETE NO ACTION to CASCADE, and both referencing columns get the index Postgres does not create for you. The comparison runs on load and lists each constraint change per table, verified in Postgres in your tab.
- Paste the tables as they are now into Before, including the parent table the new key will point to.
- In After, write the foreign key the way you would in CREATE TABLE: inline REFERENCES or a named CONSTRAINT ... FOREIGN KEY, with the ON DELETE / ON UPDATE action you want.
- Press Compare. The Changes tab lists "Add foreign key" for new keys and "Change foreign key ... ON DELETE NO ACTION → CASCADE" for keys whose action differs.
- Open Migration: foreign keys are added after columns, constraints and indexes, so the referenced key exists first. Check that it says Verified.
- Before production, find rows that would break the key: SELECT p.id FROM posts p LEFT JOIN authors a ON a.id = p.author_id WHERE a.id IS NULL. Fix or delete them, then run the script.
What to know
ALTER TABLE posts ADD CONSTRAINT posts_author_id_fkey FOREIGN KEY (author_id) REFERENCES authors (id) checks every existing row, and while it does it holds a SHARE ROW EXCLUSIVE lock on both posts and authors, which blocks writes to both tables. On a large table, split it in two: add the constraint with NOT VALID, which is instant and enforces the key for new writes only, then run ALTER TABLE posts VALIDATE CONSTRAINT posts_author_id_fkey, which scans existing rows under a lighter SHARE UPDATE EXCLUSIVE lock that lets normal reads and writes continue.
An orphan row (a post whose author_id points to a deleted author) makes the ADD CONSTRAINT or the VALIDATE fail with "insert or update on table posts violates foreign key constraint". The LEFT JOIN query in the steps finds them. Decide per case whether to delete the orphans, point them at a placeholder parent, or make the column nullable and set them to NULL.
Postgres cannot change the ON DELETE action of an existing foreign key in place; ALTER CONSTRAINT only changes deferrability. To move comments from NO ACTION to CASCADE the constraint is dropped and added again with the new action, which is what the generated migration does, inside one transaction so there is no moment without the key. CASCADE deletes children with the parent; RESTRICT and NO ACTION refuse the delete (NO ACTION checks at the end of the statement, or at commit if the constraint is deferred); SET NULL needs a nullable column.
The referencing column is not indexed automatically. Without posts_author_id_idx, every DELETE on authors scans posts to look for children, and a cascading delete on a large table can hold locks for a long time. Primary keys and unique constraints on the referenced side always have an index, so only the child side needs one.
Updated · Cosmovex
Questions
How do I add a foreign key to a large table without blocking writes?
Add it with NOT VALID first: ALTER TABLE posts ADD CONSTRAINT posts_author_id_fkey FOREIGN KEY (author_id) REFERENCES authors (id) NOT VALID. That is quick and applies to new rows. Then run ALTER TABLE posts VALIDATE CONSTRAINT posts_author_id_fkey, which checks old rows while allowing normal reads and writes.
How do I change ON DELETE NO ACTION to ON DELETE CASCADE?
Drop the constraint and add it again with the new action, in the same transaction. Postgres has no command to change the action in place. The tool writes both statements in the right order and verifies that the result matches your After schema.
Does Postgres create an index for a foreign key?
Only on the referenced side, where a primary key or unique constraint already provides one. The referencing column (posts.author_id) gets no index unless you create it. Without it, deletes and key updates on the parent table scan the child table.
Why does adding the foreign key fail with "violates foreign key constraint"?
At least one existing row points to a parent that does not exist. Find those rows with a LEFT JOIN from the child to the parent where the parent id IS NULL, fix or remove them, and run the migration again.
Is the foreign key name compared?
The definition is compared, not just the name. If only the name differs, the constraint is renamed rather than recreated. If you write an inline REFERENCES without a name, Postgres names it table_column_fkey, and that is the name the migration uses.
The free plan covers everything on this page. Schema Diff Studio Pro ($29, paid once) is described on the Schema Diff Studio page.
