>I have a table with one tag of primary type (compoud .cdx. index file), If I delete a record with command DELETE, and try again insert the same record with command INSERT-SQL, I have the following error message "Uniqueness of index is violated". I have to do the PACK command to insert this record, but I can´t open the table in exclusive mode to do the pack, because I`m programming in shared acess. I do not know why this happen because the SET DELETED is ON.

The problem you've encountered is that marking a record for deletion does not physically remove the record from the table, so when you attempt to add another record with the same primary key, the key is still present in the table, and hence the error that you're seeing. This is exactly the behavior expected 9and even documented) for VFP.

There are a couple of approaches to avoid the problem. The first would be to add a FOR clause to the tag you currently use as your primary index. If your current index expression were cMyUniqID, you could make it cMyUniqID FOR ! DELETED(). This would exclude deleted records from inclusion in the primary index. The drawback of this is that indexes with FOR clauses cannot be used for Rushmore optimization, so you'd need to maintain a separate tag on cUniqID to let VFP use it for optimized retrieval.

Another approach would be to alter the key of deleted records before issuing the DELETE command. For example, if you currently have an 8 character wide key cUniqID, you could make it 9 bytes wide instead. You could alter the extra byte (change it from the default value of space to something else) immediately before issuing the DELETE. This is not a very good solution for a number of reasons - there are consequences involving relational integrity, you'd have to do some recoding if you currently DELETE more than 1 record at a time, and if you delete a record with the same ID more than once, you're going to get bitten by the unique key again.

I prefer to use a surrogate key (a field that has no reason for existance other than to provide a unique identifier for any record for relational purposes) and never reuse a key once it's assigned. Using only positive integers, you'll run out of keys after you add record 2^31 to your table, which is a consideration with very large data sets. Using an 8 byte wide character field and encoding a number into it makes allowance for a truly horrific number of records. Since VFP can only have 2G records in a single table at once, it isn't likely to be a problem.



>
>Thanks