>>>>You touched the reason why primary key should be surrogate, e.g. sequential number, which will never be repeated.
>>>You can define your primary index to "fieldname for not deleted()". There's been a bit of discussion on this subject, but I think that it is a valid alternative.
>>
>>Larry,
>>
>>Uh oh, here come the RDBMS police. It is NOT a valid alternative to use a filtered PK index because if there is a record in teht able (deleted or not) that has the same value for the PK then there is a violation of referential integrity, period. No excuses! No exceptions! No alternatives! This is one prime directive of relational systems, the primary key is sacrosanct and cannot be violated in any way by any means, if you do then you accept the possibility that the whole integrity of references between tables will fall apart like a house of sticks.
>>
>>The only alternative is to use surrogate PK fields that never get repeated values and have no meaning in life other than their role as PK. This way you don't care about deleted records being packed or not because you NEVER try to reuse a PK value.
>>
>>Of course the limitation fo the surrogate key is that you have a finite number of values available, so use an integer field. They will give you over 2,000,000,000 unique positive values and VFP can only handle 2,000,000,000 records in a table so you don't have any problems.
>
>Wow Jim! Can you be little more specific? :)))
>
>Just to add my two cents worth, what Jim have said about using surrogate PK field should, IMHO, be permanently embedded in all developers. Period. You don't know how perfectly good non-surrogate-meaningful PK fields (ie ss#, DL#, etc) end up having to change all those not too little child tables FK values!
>
>Thanks Jim.
>John.
I am actually using a combination of both, I use a computer generated surrogate that in most cases is m.loc+dtos(date())+sys(2019), where m.loc is the computer/workstation id code (for use in lan/wan apps). I use the deleted filter in the primary key so I can use the SQL delete/update feature so I can have a transactional audit trail of all changes made. Is there a better way to be able to track any and all changes made to a table and be able to report and/or undo them if required?