>>>>I have a table where 3 character fields are combined to form the primary key. Because of the manner in which this table is now used (not as originally designed), a record can be deleted from the table by one user and later another user can add a record with the same key values. Auditing constraints require the deleted record (with datetime stamps) remain in the database; packing and recycling is not allowed.
>>>>
>>>>Of couse I understand using a surrogate key would be best, but I'm not a liberty to add fields to the table. I'm changing the index via a new "re-indexing" routine added to the executable (a one-time deliverable).
>>>>
>>>>In effect, I need as the index "cField1 + cField2 + cField3 + NOT DELETED()".
>>>>
>>>>or, if you prefer, "Field1_c + Field2_c + Field3_c + NOT DELETED()".
>>>>
>>>>Any help will be greatly appreciated.
>>>
>>>Could you use this:
>>>INDEX ON Field1_c + Field2_c + Field3_c .... FOR NOT DELETED()
>>
>>This sounds good, I'll give it a try. However, since this creates a "regular" index, (I was trying alter table add primary...), how do I ensure only one active record is allowed for the combination of field1 + field2 + field3. Note, any number of deleted records can exist, but only one can be "active". Also, this is a networked, multi-user application, if that helps.
>
>You can maintain 'uniqueness' either by using 'UNIQUE' or 'CANDIDATE' (depending on your purpose) clause, or by validating user entry when he/she saves a record.

Thanks Ed. I did just that (candidate) and it works great. The table has no primary key, but with the candidate key the uniqueness I wanted is maintained.

Dan - I could never get "field1+field2+field3 for ! DELETED()" to work in the "alter table add primary..." command; constantly complained of syntax error no matter what I did. It works in the "index ..." command. This might be because I'm using VFP 3.0 (no "b'), but I'm sure I just missed something.

Jim - Yea, I knew about "unique'" and what it really does. I thought it was great when we went over it in class, and have it in mind for a future enhancement to my main application.

Again, thanks everyone.