Jim,

>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.

Technically spoken, you're right: It is not the same. IMO, Logically it should be the same.. Developers should look at deleted records as they don't exist, like in other development enviroments. I'll guess we've got different opinions here.

>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.

Again, note that if one regards a tuple as a non-deleted record the deleted records fall outside all Relational rules and for this are not a part of a relation (table).

>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.

Yep, this a performance, concurrency, I/O reason at the moment of the update. However this not required by the RM, nor does the opposite means that you'll be having problems in this area.

>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).

Again, performance issues. I'm not that comfortable with changing intelligent PK's, they often lead to confusion in an organisation.

>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.

Here you are shifting the problem. If the table has another intelligent key (articleno) you'll still have to check for uniqueness, which IMO is best done with the help of a filtered candidate key.

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

True, the opposite is also true. All DBMSs support the use of intelligent keys.

>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.

My question is, what will you do when you're asked to develop a VFP application for an existing DBMS (let's say ORACLE) which uses intelligent keys for some tables which you want to download into a VFP database ?

>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.

Agreed !

What will you answer if someone has a medium sized application running, in which intelligent keys are used and has an uniqueness violation error ? Are you saying that he should reqrite hist application to use generated PKs or are you saying to filter the Primary indexes ?

Walter,