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

An index on PK + Deleted() will allow for the FK references wihout them having the deleted data in the FK.

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

But it is there, how can one say it doesn't exist even though it is there? The RM concerns itself with the records in a table (regardless of the specific implementation). So my take is that when VFP marks a record for deletion, as far as RM is concerned the record still exists because it physically resides in the table.

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

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

The above definition of Referential Integrity is taken from C.J. Date's book "Relational Database Selected Writings" Addison-Wesley ISBN: 0-201-14196-5, 1986 on page 35.

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

This is a toll feature not part of RM. C.J.Date says on page 148 of the same book;

"Aside: 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. Again, confusion on this point is not uncommon. Arguments and counterarguments can be found in references."

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

I will not argue that VFP is NOT a RDBMS, neither is Oracle nor SQL Server. They are simply tools that allow the use of relational designs.