Re: RI Types

At 06:27 PM 12/2/00 -0500, Michael Fork wrote:
>I am trying to set the update and delete rules that are returned from the
>ODBC driver and the spec has the following to say:
>
>SQL_NO_ACTION: If a delete of a row in the referenced table would cause a
>"dangling reference" in the referencing table (that is, rows in the
>referencing table would have no counterparts in the referenced table),
>then the update is rejected. (This action is the same as the SQL_RESTRICT
>action in ODBC 2.x.)
>
>What I need to know is if RI_FKey_noaction_del and RI_FKey_restrict_del
>procedures are functionally the same. The ODBC (which I would hope
>conforms to SQL 9x) spec has 4 types of RI (CASCADE, NO_ACTION, SET_NULL,
>SET_DEFAULT), and Postgres appears to have 5 (RI_FKey_cascade_del,
>RI_FKey_noaction_del, RI_FKey_restrict_del, RI_FKey_setdefault_del,
>RI_FKey_setnull_del), which leads me to belive that restrict and noaction
>are the same thing, and the one that is used depends on what the user puts
>in the REFERENCES line.
>
>Am I correct?

"RESTRICT" is a SQL3 thing, an extension to SQL92. It appears that the
intent is that restrict should happen BEFORE the delete goes chunking
its way through the tables, while noaction tries to delete then rolls
back and gives an error if necessary.

The final table entries are exactly the same for the RESTRICT and NOACTION
cases, so the semantics in the sense of the transformation that occurs on
the database are equivalent.

Currently, PG treats NOACTION and RESTRICT as being the same, they're
separated in the code with a comment to that effect, i.e. the code for
NOACTION is duplicated for RESTRICT (in part to make it clear that
in the future we might want to implement RESTRICT more efficiently if
anyone figures out how).

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.