Walter,

The key point being discussed was deleted records. In xBase there is a state called deleted that is not the same as a record that has been removed from the table. Other DBMSs do not have this state for their records. Since VFP has this "deleted" state it must be accomodated. The argument here is related to the misinterpretation that a "deleted" record in xBase is actually the same as a record that has been removed from the table, it is not the same.

In Date's statement that a tupple must be unique, at least, in the sum of its attributes, is met by xBase even with the reuse of PKs values because the deleted flag is different. However, the common use of the detelete state in xBase is to represent records that would not exist in other DBMSs. Since they do exist in xBase tables they MUST be accounted for.

They can be accounted for by using a filter Primary index, but this causes the Primary index to NOT be based on the Primary Key, which by definitiion must be unique for ALL records (including those in the xBase deleted state).

The deleted state is only one reason for using surrogate PKs. My reasons are as follows;

Use Surrogate keys because;

1) They are guaranteed to be unique and they are not user entered. Users make mistakes and they want to correct them. Changing PK values causes a fall out into the RI area. If the PK never changes then there is no requirement to handle the related RI issues.

2) The purpose of the PK is for the computer and/or the DBMS to associate records with one and other. Using a Key that is program generated does this fine without additional issues of user intervention.

3) If a person knows the value of an attribute they will, eventually, want to change that value. With most attributes this may, at most, reuqire an enforcement of uniqueness. With PKs this also involves potential mass changes to other tables (updating FKs).

4) A surrogate PK is easy to keep unique as it carries no meaning and therefore can be forced unique by a program. Using surrogate PKs removes the need to test for uniqueness during data entry and the associated actions on the part of a user to determine a unique value.

5) All DBMSs support the use of surrogate keys so this can be universally applied.

These are my major reasons for using surrogate keys. I use them consistently for all tables because I find consistency is more beneficial than the alternative.

Plesa note that in my major reasons for using surrogate keys ther is no mention of the deleted status of a record. The role that surrogates play in that issue is a nice side effect, in my opinion.