Re: UPDATE catch-22 - help!

Hi Joost,

in Oracle8 the deferred constraints solve your problem.

(1) set constraint fk_payment deferred

(2) update payment set personid = 4711 where personid = 0815 and transactionid = 08

       ( the personid 4711 does not exist in table 'credit' )

(3) update credit set personid = 4711 where personid = 0815 and transactionid = 08

(4) commit

The constraint 'fk_payment' will be checked after the commit, not after (1).
The deferred contraint concept is also available for primary key-constraints and unique-constraints.

Hope it helps.
regards, Jan

Joost Ouwerkerk wrote:

> Two tables:
>
> PAYMENT (transactionID, PersonID, amount, paymentdate)
> CREDIT (creditID, transactionID, PersonID, CreditPersonID)
>
> The credit table logs people who get credit for payments made by other
> people. A credit record can only be created for existing Payments and
> this is enforced by a constraint.
>
> The two tables are linked by a primary key that consists of the
> transactionID and the PersonID
>
> If I try to update the PersonID on the PAYMENT table, I get a 'child
> record found) constraint violation.
>
> If I try to update the PersonID first on the PAYMENT table, I get a
> 'parent key not found' constraint violation because a PAYMENT record
> with that combination of transactionID+PersonID does not yet exist.
>
> How do I UPDATE the PersonID?
>
> Joost Ouwerkerk
> Toronto, Canada
Received on Mon Dec 07 1998 - 06:03:09 CST