Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Carch-22 was: optionality and cardinality

Carch-22 was: optionality and cardinality

From: David Cressey <david_at_dcressey.com>
Date: Fri, 28 Sep 2001 11:24:28 GMT
Message-ID: <M5Zs7.109$h4.4302@petpeeve.ziplink.net>


Andrea,

The case you have outlined, where you can't insert into one table unless you insert into another table
"at the same time" is a well known case. I know it as the Catch-22 problem, but it may have a different
name in the literature.

Here's how to get around it. You establish the two referential constraints (perhaps by a FK in each table, referencing the PK in the other table) but you use an option in each create constraint that defers constraint evaluation until COMMIT time.

Then, when you do the inserts, you are careful to insert into both tables, in the context of the same transaction.
At the time you do the two INSERTs, the constraints won't be evaluated. By the time you do the COMMIT, both
tables will have been updated "at the same time".

This won't work if your RDBMS doesn't support deferring constraint evaluation until COMMIT time.

hth,

--
Regards,
    David Cressey
    www.dcressey.com
Received on Fri Sep 28 2001 - 06:24:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US