Re: UPDATE catch-22 - help!

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?

You don't.

Add a new record in the PAYMENT table that has the new PersonID but is otherwise the same.

Update the CREDIT table's personid to the new one.

Delete the old PAYMENT record.

----dale Received on Thu Dec 03 1998 - 15:25:59 CST