Jim,

Interesting observations.
>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".

You are right that the proposed PK has nothing to do with a filtered Primary index. To use that key the index expression woudl be something like IIF(DELETED,"A","B") + OtherField

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


Primary keys come in two basic flavors, natural and surrogate. Natural pks are pks comprised of fields that would still be part of the record even if they weren't being used as the PK. Surrogates have only one purpose in life, to be the PK. If a surrogate was not used as the PK it would not be part of the record at all.