Re: UPDATE catch-22 - help!

Normally you don't want to update primary/foreign keys. But it is certainly possible.
Look at your constraint definition. Make it cascade - or deferred. Either will work.
If you create a cascade constraint, then updating the master record will automaticly update all references to this master record. If you work with a deferred (Oracle8) you can do your update as you describe and Oracle will only validate the constraint on commit.

Regards
  Peter Larsen
  Senior Oracle Consultant

Joost Ouwerkerk wrote in message
<3666cbbc.7879119_at_resunix.sickkids.on.ca>...
>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 - 16:03:07 CST