Search Postgresql Archives

Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



AWS RDS Postgresql 12.10

https://www.postgresql.org/docs/12/sql-createtable.html

[quote]
DEFERRABLE
NOT DEFERRABLE

This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction [/quote]

[/quote]

But yet a DEFERRABLE FK constraint in a transaction immediately failed on a FK constraint violation.

[quote]
INITIALLY IMMEDIATE
INITIALLY DEFERRED

If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. 

[/quote]

INITIALLY DEFERRED solved my problem.  Why do both clauses exist?

(A naive interpretation just by looking at the clause words led me to think that INITIALLY DEFERRED would not check record validity when a constraint is added to a table, but obviously that's wrong too.)

--
Angular momentum makes the world go 'round.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux