General information
Category:
Coding, syntax & commands
>>>>>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
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only