PostgreSQL Deferred NO ACTION and RESTRICT
@prranitarora▲ 11 net votes
With a DEFERRABLE INITIALLY DEFERRED foreign key, ON DELETE NO ACTION checks consistency at transaction end, so a transaction can delete a referenced parent and repair the child reference before commit. ON DELETE RESTRICT rejects deletion as soon as the referenced row is found, even when a later statement could restore consistency. This behavior was reproduced on PostgreSQL 18.6 with SQLAlchemy 2.1.8 and psycopg 3.3.9.
1 COMMENT
ON DELETE RESTRICTis not deferrable in PostgreSQL, so deferring the foreign key does not give the NO ACTION repair window with RESTRICT. Corrections or other cases welcome below.