Jim,



>The implementation of RI features in a product has nothing to do with what Referential Integrity is. VFP provides triggers that fire at the right time for enforcing RI, however, the code in those tirggers is not limited to RI code. Referential Integrity is simply protection of the PK-FK relationships.

Of course this is correct. But if you look at how RI is implemented by the VFP team (by triggers, but IMO that is irrelevant here), you can only conclude that this RI mechanism DOES NOT consider the existance of deleted records. There is simply no other comfortable way to implement RI within VFP, because deleted records CANNOT be fysicaly deleted without packing the table. Therefore all keys, including primary key's should be scoped to nondeleted records. For foreign key's there is no problem to let the scope be the whole table, but for primary keys which have a unique constraint this is real problem.


For example: If you use intelligent keys and you use views with the DELETE AND INSERT update option you're in real trouble if you don't scope the primary key to nondeleted records. In other RDBMS's this problem does not occur, which is proving that this issue is caused by the VFP implementation (or lack off) of the relational model.


>I don't agree that includng the deleted flag in the Primary index expression is silly, in fact it is the only right way to do it. To not include the delete flag is to create a primary key that fails the first rule of uniqueness.

Come on Jim... then you must include this deleted flag within each foreign key. If this isn't silly, i'm george foreman. And again this doesn't solve the problem. Since I want to ignore the existance of deleted records (I think the vast mojarity of all VFP developers do) you must be able to have multiple deleted records with the same value in the primary key field(s).

>>The actual reason for this filtered primary index is thus, that VFP tables actual stores records (for internal VFP implementation reasons) which are deleted. For this matter I don't think the deleted mark can be regarded as a normal field.

>Does the record exist or not? If it exists then it needs a unique primary key value.

As far as I and the RI mechanism concernes, it does NOT exist, therefore it should NOT have a primary key value. As far as VFP internals, it does exists. I'm not willing to adjust my primary key assignment considerations because of some internal VFP mechanisms.

>Referential Integrity is defined by Date as ... "The base relation R2 inclkudes a foreign key FK matching the primary key PK of base relation R1 (R1 and R2 are not necessarily distinct), then every value of FK appearing in R2 must either (a)appear as a vlaue of PK in some record of R1, or (b) be NULL. (case b allows for the possibility that - for example - a given employee may currently be assigned to no department at all)."

I did a search in my documents, and you may be right on this issue, though I could not find any prove that it was prohibited: the relational model only uses the PK for RI. However the SQL implementations of VFP and SQL server do allow RI constraints on candidate keys. Personally I can't find any convincing reasons not to use a candiate key when a (intelligent) candidate key is preferred. What are the implications if I do ??

BTW. The use of .NULL. for nonreferencing foreign keys value is another matter. Isn't it DATE who publicly doubted if the .NULL. values should have a place within the relational mode ?

>If one desings a database where a table T1 is related to multiple other tables T2 and T3 and the PK in T1 for each of the other tables is different, then T1 does not actually have a PK, it is trying to function with multiple PKs and that is a violation of the relational model (each table has one PK).

see the above.

>Breaking normalization and storing additional vlaues from T1 in T2 or T3 for query perrofmance does not make those additional field part of a relation. They are simply redundant data that exists for reasons outside of the relational design adn it is the responsibility of the programmer to protect their consistency. Simply because a programmer decides to do this using triggers does not make those rules part of RI.

I don't have any backups, but I suspect that with many SQL implementation you can define RI contraints on candidate keys. At least I found the following line in the SQL server training guide:

- FOREIGN KEY constrains must reference either the PRIMARY KEY or a UNIQUE (Read Candidate or alternate key) constraint.

Finally, don't forget that the RM is a dynamic theory. Many GURU still have other perceptions of the RM (Like between CODD and DATE). On the other side: VFP is a very, very poor implementation of the RM. Even Oracle is only a 60% implementation of the RM. Given this fact, it's doubtfull we can apply a given written rule within VFP. Implementation issues might force you to break certain rules. In my case I feel that its justified to add a filter on the primary index.