Re: Insertion Deferrable

On Fri, 29 Oct 2004, [UTF-8] ^]^B^U wrote:

> You told me restricted foreign key cannot deferable in PostgreSQL 8.0.0 Beta 4.
>
> 2004-10-21 22:33 tgl
>
> * doc/src/sgml/ddl.sgml, doc/src/sgml/ref/create_table.sgml,
> src/backend/commands/tablecmds.c, src/backend/commands/trigger.c:
> Disallow referential integrity actions from being deferred; only
> the NO ACTION check is deferrable. This seems to be a closer
> approximation to what the SQL spec says than what we were doing
> before, and it prevents some anomalous behaviors that are
> possible now that triggers can fire during the execution of PL
> functions. Stephan Szabo.
>
> Take this test...
> Restricted foreign key allows insertion deferrable,
> And rejects deletion deferrable.

There's two separate concepts in foreign keys that are related.

There's the constraint check which makes sure that the constraint is
satisfied (ie, there is an appropriate matching row for each row from
the referencing table). This is deferrable.

There's also referential actions which occur when you change the
referenced table. These are not deferrable.

---

When you insert into a referencing table, at constraint check time (which
may be deferred), the check is applied.

When you delete from a referenced table, if there is a referential action
associated with the foreign key, it is done non-deferred. Technically,
the SQL model would also have a check a constraint check time, however in
your case the error from the referential action happens before that would
occur.