Recommended Free Tools
In PostgreSQL, add the foreign key as NOT VALID, then run VALIDATE CONSTRAINT as a separate statement. This isn’t lock-free. The first statement still takes SHARE ROW EXCLUSIVE locks on both the referencing and referenced tables. But it skips the scan of existing rows, which is the slow part. The scan moves into the validation step, which uses weaker locks that PostgreSQL’s documentation says don’t lock out concurrent updates.
The two-step procedure
Before running anything, confirm that the data types and column mapping match. Decide the MATCH, ON DELETE and ON UPDATE behavior you want. Check that the referenced columns are eligible (see below) and that your role has REFERENCES permission on the referenced table or columns.
Step 1: add the constraint without scanning old rows
ALTER TABLE child_table
ADD CONSTRAINT child_parent_fk
FOREIGN KEY (parent_id)
REFERENCES parent_table (id)
NOT VALID;This skips the potentially lengthy scan of existing rows. It still takes SHARE ROW EXCLUSIVE locks on both tables, so the statement is brief but not invisible. Once it commits, the constraint is enforced for every later insert and update.
Step 2: validate separately
ALTER TABLE child_table
VALIDATE CONSTRAINT child_parent_fk;Validation scans the referencing table for rows that violate the constraint. According to the PostgreSQL 17 ALTER TABLE documentation, it takes a SHARE UPDATE EXCLUSIVE lock on that table and, for a foreign key, a ROW SHARE lock on the referenced table. Concurrent updates can proceed because new and changed rows are already checked by the constraint. The documentation puts the purpose this way: “The main purpose of the NOT VALID constraint option is to reduce the impact of adding a constraint on concurrent updates.”
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minute#1 Best Overall
One-shot versus staged
| Question | Plain ADD FOREIGN KEY |
NOT VALID then VALIDATE |
|---|---|---|
| When are existing rows scanned? | Inside the ADD statement |
Later, in VALIDATE CONSTRAINT |
| Locks during the add | SHARE ROW EXCLUSIVE on both tables, held through the scan until commit |
SHARE ROW EXCLUSIVE on both tables, but without the scan |
| Locks during the scan | The same strong locks, which block updates until commit | SHARE UPDATE EXCLUSIVE on the referencing table, ROW SHARE on the referenced table |
| Writes during the scan | Blocked | Concurrent updates not locked out, per the documentation |
| Old violations | The whole statement fails | New violations are already prevented. Validation can be retried after cleanup. |
Because even the quick first step needs strong locks, consider setting a lock_timeout in the session. That way the statement gives up rather than waiting indefinitely behind a long-running transaction.
Handling existing orphan rows
NOT VALID is especially useful when old data may already break the relationship. New violations are blocked as soon as the constraint exists. Meanwhile you can find and repair old orphans, then run validation again. Validation only succeeds when every existing row satisfies the constraint.
Rank #2
For a simple single-column key, a preflight query can find offenders (an illustrative query, not a tested one):
SELECT c.parent_id
FROM child_table AS c
LEFT JOIN parent_table AS p ON p.id = c.parent_id
WHERE c.parent_id IS NOT NULL
AND p.id IS NULL;Adapt it for composite keys, custom match semantics and nullable columns. VALIDATE CONSTRAINT remains the authoritative check.
Free tools Windows power users keep installed
One-click scans. No signup required.
Key and index requirements
The referenced side
The referenced columns must be a primary key, a non-deferrable unique constraint, or the columns of a non-partial unique index. For composite keys, verify the column order and uniqueness on that side.
The referencing side
PostgreSQL does not automatically create an index on the referencing columns. The CREATE TABLE documentation says adding one may be wise when referenced keys are changed often, since referential actions can then run more efficiently. Treat that as a workload decision, not a rule. Building an index on a very large table is its own operational change, so plan it separately.
Matching and actions
MATCH SIMPLEis the default: any null component exempts the row from needing a referenced match.MATCH FULLrequires either all components to be null or all to match.NO ACTIONis the default and raises an error when a delete or update would leave referencing rows invalid.CASCADE,SET NULLandSET DEFAULTchange data in different ways, so don’t add them casually.
Partitioned tables and version caveats
The PostgreSQL 17 ALTER TABLE documentation states that foreign-key constraints on partitioned tables may not be declared NOT VALID at present. Check the documentation for your exact major version and table layout before using this recipe on partitioned relations. Don’t assume the ordinary-table procedure carries over.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
