avoiding 'Uniqueness' error on primary key swap

[alan232]

Hi All;

What is the best way to swap the primary keys between two
records in the same table?

The basic situation is an accounting problem:
I have one person who has two legally different records in a table, zPeople. zPeople has children related records in other files for which Insert/update/delete are all cascaded on the primary key. Its easy enough to move all the children records from one primary key in zPeople to another as long as I don't touch either of the two records in zPeople. However, after moving the children records, I would like to keep the primary key of the most current (of the two) records in zPeople--which means at times moving the primary key from one record to another ( or swapping them), then deleting the no longer used record/primary key.

However, even if the record in zPeople to be deleted has its primary key changed to an unrelated value, putting its original value on another record causes:
'Uniqueness violation...error'

Surely, swapping primary keys in a relationtional database is a common manuever, but I can't seem to get arround this error.

Thanks for any help in advance!
Alan

 

[bborissov]

Alan,
If you use VFP9 make the index that is a Primary filtered to NOT DELETED(), that way you can delet one record and put itsd key to other w/o error. VFP9 still can use this index for Rushmore.
If you use VFP 8, Go to this record to which you want to attach all child records, SCATER it, replace the PK with something that you are sure not exist in the table and then delet that record. Then go to the record with Child records and do GATHER FIELDS EXCEPT PK.

Borislav Borissov

 

[alan232]

Hi Borislav;

Thanks!
I'm using vfp 6.0. Actually, this problem is a specific instance of the more general problem--merging two databases from seperate offices. I've written a general alogorythm to merge all the files (about 45). zPeople is the most important file, however. I've tried not to change the existing indexs--but I'm thinking your probably right:
Adding a
'not deleted()'
to the primary and candidate indexs maybe necesarry.

Too bad there is not a way (that I know of) to temporarily suspend the RI rules long enough to make the changes. Then reindex() or something similiar.

Thanks,
Alan

 

[alan232]

I forgot to add, I'm trying to keep as many of the children from BOTH records as possible but drop one of the two parent records...i.e., do a system wide merge.

Thanks again,
Alan

 

[alan232]

Hi All;

Just a concluding note for anybody interested. The basic problem is that 'replace' implicitily calls tableupdate() regardless of the buffering mode of the table and that 'delete' does not remove the entry in the index. Hence, the trick is to completely delete the no longer used parent record after its children have been moved and PACK the file. Once packed (and the index is being fixed), the key values for the primary and candidate indices can be again used for the new parent record.

Thanks again for your suggestions,
Catch ya'll on the back side
Alan

 

[Olaf Doschke]

Hi Alan,

Swapping IDs is not at all a common manuever.

I see the problem which arises, if databases of two or more offices need to be merged. I'd not merge one of the databases into the second, but merge both databases into one single new third database. Then you can generate a third id for a Person and point back to the two old ids.

I'd generally create a data transfer application and include historical ID fields in the new database to be able to link back to old data in case something went wrong in the transfer process. These fields can be deleted if the process is done, but might even stay there for a long while...

Bye, Olaf.

 

[alan232]

Hi All;

Actually, all my primary keys were generated using sys(2015). The basic problem has occured becuase some people have a tendency to go to different offices and identify themselves with different names. So what has happened over the years is that the same record initially existed in all three offices, but in one or more of those offices, the name field, birhtdate, etc. was changed by the staff--people get married, divorced (or just plain lying). When this happens, the primary key no longer points to the same name data and would appear to be two seperate people--and each record results in two different physical charts. Eventually, the system has started to spiral making billing a nightmare when the same physical person has two or more computer accounts.
I've toyed with the idea of making a third database to resolve the issues, but since vfp kindly provides the mechanism to echo a change in the primary keys down to the children, it seemed like unnecessary work.
A history is a great idea--especially since I hate 'automatic' changes that a person can't follow--so I built in a record keeper for any conflict before and after a change. This way, I at least, can track the changes that were made--this is especially relevent since if a chart number is lost, so will all the records be--not a good thing.

Thanks again,
Alan