Hi Jim,

I feel the urge to add a comment or two here. . .

>The delete marked records are in the table, the relational concept of a primary key is not tied to implementation but to logical design. If the record is there then it must be unique. Using a filter on a primary index allows you to have two records with the same primary key value in the same table, a clear violation of the primary key.
>
Assuming the fields F1, F2 and F3 where F1 is a "key" field (K1) and F3 is really the DELETED flag (K2) then using the definitions you so graciously provided earlier, there could be at least two records - one with DELETED of .T. and one with DELETED of .F.. Of course this would not be a "filtered index".

>>Well I doubt this. I wonder what Chris Date says about this... From a pure relational approuch this may sound right. But from a practical approuch it's generally a bad idea to change a (from the users perspective) primary key. Let's talk about the articleno. If you change the articleno, all historical data will be changed. Earlier printed documents (like invoices) refers to primary keys (again from the users perspective) that has changed. This usually leads to confusion and has to be avoided as much as possible.
>
>A primary key is a relational thing, it has nothing to do with the data that describes the entity. it is the glue that holds the relationships together. Once you sue a surrogate PK then all other potential keys become candidates. A potential key is a field or group of fields that uniquely and umambiguously identifies a specific record. If there is any other record in the table wqith the same value then the fields in question are not a candidate key. A candidate index is something else altogether, it is an index that denies the creation of a duplicate record for the index key expression.
>
While I agree that it (primary key) can be interpreted as "it has nothing to do with the data that describes the entity", it has always been clear to me that the general case is to use data fields themselves to define the key and that it is a particular combination of data fields that must be UNIQUE through the whole set.

Cheers,

Jim N


>Using your example, with a PK of ArticleID and the ArticleNo as a field, if the user changes articleNo to correct a mistake (since articleNo has meaning other than relational and must be allowed to be accurate rather than simply unique).
>With a surrogate key you simply let the user change the articleNo and the relationships are intact because they don't use the articleNo they use the ArticleID that the user nevers sees and has no idea it exists.
>
>>Another and certainly not important issue, is that if you use intelligent keys, it sure is easier to analyze the contents of pure tables when debugging applications. In the occasion of a Primarykeychange (whenever it is allowed) there is no objection against a cascade update of the primary key.
>
>I have heard people say this but I have never had a problem with it.
>
>>According to the relational model there is no difference between a candidate and a primary key. Therefore primary key indices should have the same features as a candidate key index.
>
>There is a difference, one is used as the primary key and the other is not. There is only one Primary key to a table, candidates are simply things that may or may not be used as the primary key. Once the primary key is selected then the candidates are meaningless in terms of PK and FK relationships within relational logical design. Candidates just become other fields. Don't confuse a candidate key with a candidate index, they are not the same at all.
>
>The only place where candidates get special consideration with relational design is for Boyce-Codd Normal Form. Once that is done they are simply fields in the table as far a relational theory and referential integrity is concerned. Now business rules are a different issue here.
>
>>There is really no objection to use candidate indexes for referential integrity. In fact in some cases it might a reqiurement to have two of such keys used for RI.
>
>RI never uses anything but the PK and the FK, anything else in RI is not RI. Referential Integrity is protecting the integrity of the PK to FK references.