uniqueness of index <insert index name> is violated.

JDPeckham

Hi, i'm making batch changes to several of our foxpro databases using C# and ado.net. Basically i am consolidating many lookups into the same key across multiple databases.

After moving them to the proper id and deleting unused id's, i go and find out from all of the databases what lookups they have, compile them into a uniquelist and then make sure that every database has that row. My problem is that once my consolidation happens and it deletes unused rows, i'm trying to add rows that use to be there back in because they're part of my 'master list' and i need them even though they aren't being used...

i then get this error even though the record has been deleted already.

example

id 9060 was not being used, i first go through and delete all codes that aren't being used in related tables (to clear out all of the junk --and unfortunately in some cases codes i need to keep around as well)

9060 was deleted

I consolidate all of the codes

i go to my master list and try to add 9060 again with it's proper description (one that matches the master list), and i get the uniqueness error.

Now, i could go back and rewrite the whole architecture of my software to support this 'bug ' but i'm hoping someone knows a way to rebuild the indexes programmatically (in ado.net) so i don't have to.

Thanks!

Dan Freeman

There's no bug here, just an under-understanding on your part.

Deleting records in Foxpro does not remove them from the table until the table is PACKed, so that when you try to re-insert them they already exist.

AndyKr

As Dan told you, the issue is that records are not removed until you physically PACK the table (See PACK vcmmand in Help for a detailed explanation).

Since you have a restricted index (either Candidate or Primary) one solution is to add a filter to the index condition as "FOR NOT DELETED()". This is not normally recommended in an application environment (filtered indexes are not optimizable and reduce performance), but for data cleansing it may be acceptable.

The DELETED() function returns a boolean TRUE if the record is flagged for deletion - so the index will exclude all such records and you won't get this error.