>The code generated by the RI builder checks for referential integrity on deleted records.
>
>This is a total pain.
>
>Does anybody know why this was thought neccesary or is it just a load of purist nonsense that we will laugh about in a few years.
>
>Has anyone tried altering the RI code so that it does not do this?
>
>At first glance it just looks like at case of changing all the
> SET DELETED OFF to SET DELETED ON
>
>Has anyone tried this?

The DELETED() status has nothing to do with RI. A deleted record still exists in the file, and so no other record can have the same primary key with that value. The error is the result of recycling primary keys; a bad habit that should be avoided at all costs, my general approach being to use a distinct field as a surrogate key that does not carry any inherent data with it. Recycling keys causes all kinds of strange behaviors, and should be avoided at all costs from my POV.

If you absolutely insist on recycling keys in spite of this, you can modify your primary key to include a FOR NOT DELETED() clause; this will disallow the consideration of deleted records from the primary index, but can cause considerable troubel down the line, since indexes that contain a FOR clause cannot be used for Rushmore optimization of queries, and any RECALL operations that restore a duplicated key will result in an error. THis can also occur if you use certain data recovery tools that may clear the deleted flag from the last few records in a table to try to deal with certain types of data corruption.

The simplest solution is to never recycle keys; if you find yourself in a situation where you have what appears to be a primary key, but where the key may recycle to fit some busniess rule, use a separate surrogate key as a primary key, and implement the data-bearing key as a regular key, and add code in validation that uses INDEXSEEK() to check for key duplication.