Walter,

>>An index on PK + Deleted() will allow for the FK references wihout them having the deleted data in the FK.
>
>This is certainly not allowed in the relational model. The value of the PK must match the FK value. That this can be done with VFP is no reason to impelement it this way. Besides, this won't work with certain setting like SET EXACT, and I doubt if all RI functions will work properly. Even in future releases of the RI function there is no guarantee that this will work.

It is not my contention that the suggestion above is a good one, only that it is better than pretending the deleted records are not there. My solution is to use surrogate PKs everywhere and everytime. Having made this decision in my designs has made the entire issue an interest mental exercise at most. I have no problems with deleted records, filtered primary indexes oin any of the other related issue. Also the desing (using surrogate PKs) will never need to be changed adn the users have full control over the values that they work with everyday without any limitations due to PK-FK situations. I never need to take normalization beyond Boyce-Codd because I never have a PK that has more than one field in it. So, with those features of a surrogate PK, I see no reason to investigate the use of natural keys. As soon as I do that, all of the issues above are created adn I have to deal wiht them.

>According to the RM, Primary keys applies to tuples. If we say that we define a tuple as a non deleted record, there is nothing wrong with my story.

Mixing metaphores here, a tupkle is a logical design thing and a record is a real world thing. A tuple will, most probably, not have a deleted flag attribute, while when using VFP there is definitely a deleted flag.

>According to your opinion it is not possible to delete any records in a multi-user environment, because the record will still exist ?? This does not make sense to me. In which RDBMS you cannot delete records ?? In all other RDBMS primary keys don't concern deleted tuples, why should it within VFP ? That VFP stores deleted records in a sort of wast bucket is a total different issue. Especially when the available RI mechanism doesn't respect deleted records, why should any primary key ?

This is flattly untrue, I use surrogate PKs that are always unique. Using a natural PK (which I won't do for a number of reasons) would only entail my first finding any existing deleted record with the PK value and then reusing that one rather than trying to add another new record with the same PK value.

>This situation exists because deleted records are stored within the table. This is the real problem. If you can decrease the problem by making a filtered primary key, i've got no problem with this.


My problem is that it makes the primary index not on the primary key.

>This proves that again this is a topic for discussion, meaning that others do regard (or want to) candidate keys in RI as a part of the relational model.

How does "...Note that the Referential Integrity rules requiries the foreign key to match, not just some candidate key, but very specifically the primary key, of the referenced relation..." have any open for discussion interpretation?

Date states here that the rule is clear and that there is confusion about it. He does not say the issue is undefined or has alternate interpretaions that are equally sound. He clearly says that RI involves PK FK relations ONLY and no other CK can be used as FK in a related table.

>This only proves that we must implement database applications with the features a specific RDBMS offers. We should use the RM as a guideline, but given the limitations, we all can have different perceptions of how we should implement this as close to the RM as possible.

I will never argue that developers will have differeing opinions about things. I will argue that the RM is used to desing a logical databse and that when one goes to implementation one has to live within the constraints and features of tool being used. However, to consider the tool before the logical design is completed and implementation has begun is a mistake. If one does that, then when teh tool changes the design must be revisited. If instead one creates a logical model that is pure and not tool dependent, then the same logical design can be used in designing any implementation using any tool. ONe just has to make adjustments to the implementation, or physical, design to account for the limitations and features of the new tool.