Various ways of tweaking Foreign Keys are suggested that are helpful
for larger databases.
* Deferrable Enforcement Timing Clause
* NOT DEFERRABLE - immediate execution
* DEFERRABLE
* INITIALLY IMMEDIATE - existing
* INITIALLY DEFERRED - existing
* INITIALLY NOT ENFORCED
FK created, but is not enforced during DML.
Will be/Must be marked NOT VALID when first created.
We can run a VALIDATE on the constraint at any time; if it passes the
check it is marked VALID and presumed to stay that way until the next
VALIDATE run. If it fails that check the FK would be marked as NOT
VALID, causing it to be no longer useful for optimization.
This allows FKs to be checked in bulk, rather than executing during
front-end code path, but yet still be there for optimization and
documentation (or visibility by tools etc).
There is no corresponding SET CONSTRAINTs call for the NOT ENFORCED
case, since that would require us to mark the constraint as not valid.
* Referenced Table actions
ON DELETE IGNORE
ON UPDATE IGNORE
If we allow this specification then the FK is "one way" - we check the
existence of a row in the referenced table, but there is no need for a
trigger on the referenced table to enforce an action on delete or
update, so no need to lock the referenced table when adding FKs.
This is very useful for very highly referenced tables.
Or for larger tables where we aren't planning on deleting or updating
the referenced table without also deleting or updating the referencing
table.
-- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services