>>>>>>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.
>>
>>Why do you say Edward's suggestion: "field1+field2+field3 for ! DELETED()" creates a 'regular' index? I've created a primary key (in VFP 3.0b) and it works fine?
>>
>>Just curious
>>
>>dan
>
>In My Application, When I create the primary index I use the filter to .NOT.DELETED() so the index will ignore all deleted records.
>
>I hope that could also help
>
>
>Xavier

Two questions:

1) How did you do that on the primary key? I could never get past the syntax on the "ALTER TABLE ADD PRIMARY ---" command so I finally created a candidate index using the "INDEX --- FOR NOT DELETED() CANDIDATE" command.

2) Back in the Clipper world, we avoided filters at all cost on large files like this for performance reasons. Is the filer in the index command (FOR !DELETED()) different.?

Thanks,