Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Client/Server » View Thread |
Messages 1 to 4 of 4 total |
Applying Cached Updates with compound primary key |
Sat, Mar 15 2008 6:35 AM | Permanent 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 |
This web page was last updated on Thursday, March 28, 2024 at 08:36 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |