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 [EMAIL PROTECTED]
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.