Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread Applying Cached Updates with compound primary key
Sat, Mar 15 2008 6:35 AMPermanent Link

Tony Pomfrett
Hi Tim,

I have a table with 4 columns, the first 3 being part of a compound primary key. Consider the following two records:

Before editing in cached mode:
====================
1, 1, 1, RecordOne
1, 1, 2, RecordTwo

After editing in cached mode:
===================
1, 1, 2, RecordOne
1, 1, 1, RecordTwo

Calling ApplyCachedUpdates causes a 9729 duplicate key error.

I'm assuming this happens because the logic of applying the cached updates assumes no records need to be added or deleted and it is only
necessary to edit the 3rd field in each record to complete the update. Assuming the records are updated sequentially, when the first record is
updated, its first 3 fields which combine to make the primary key are the same as the yet to be updated first 3 fields of the second record and
hence we get the 9729 error. When editing the original two records in cached mode, I had to use an intermediate step like this:

1. Change record 1, column 3 to 0
2. Change record 2, column 3 to 1
3. Change record 1, column 3 to 2

I had assumed that the cached update process would recognise that neither of the two original records exist in the updated table and so they
should both be deleted from the original table before the updated records are inserted but it doesn't seem to work like that.

Do I need to add another integer column for use solely as the primary key?
Tue, Mar 18 2008 3:17 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Tony,

<< I have a table with 4 columns, the first 3 being part of a compound
primary key. Consider the following two records:

Before editing in cached mode:
====================
1, 1, 1, RecordOne
1, 1, 2, RecordTwo

After editing in cached mode:
===================
1, 1, 2, RecordOne
1, 1, 1, RecordTwo

Calling ApplyCachedUpdates causes a 9729 duplicate key error.

I'm assuming this happens because the logic of applying the cached updates
assumes no records need to be added or deleted and it is only necessary to
edit the 3rd field in each record to complete the update. >>

Not quite.  The cached updates logic doesn't assume any insert/update/delete
sequence, hence the reason for the issue.  However, picture the above
scenario without cached updates.  How would you actually accomplish what
you're trying to ?  Cached updates follows the same rules as any other
insert/update/delete into the source table.

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, Mar 19 2008 6:12 AMPermanent Link

Tony Pomfrett
Thanks Tim,

I had hoped that the process of applying cached updates worked by identifying all modified/deleted records in the cached table and then deleting the corresponding records in the real table followed by inserting all the modified records from the
cached table into the real table. Under that scenario I would have avoided the duplicate key issue. I have worked around the problem by adding an integer field as a primary key. I still have the three field compound key but it is no longer
constrained to be a unique key.

Tony.
Wed, Mar 19 2008 10:01 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Tony,

<< I had hoped that the process of applying cached updates worked by
identifying all modified/deleted records in the cached table and then
deleting the corresponding records in the real table followed by inserting
all the modified records from the cached table into the real table. Under
that scenario I would have avoided the duplicate key issue.>>

Yes, but that's also extremely inefficient and would require a transaction
for every application.

--
Tim Young
Elevate Software
www.elevatesoft.com

Image