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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
