>1) Defining Relationships and
>no impact - all it does is to add yet another field and index to your table
>you put this as a foreign key in related tables and continue as you would
>with any other relation

No, I think this doesn´t quite solve the problem. I should define the surrogate key as PRIMARY and my original key as CANDIDATE, and the problem would keep popping up. (Defining the original key as REGULAR just ignores the problem.)

>2) VFP's default Referential Integrity ( not xcase )
>
>actually this can make your life easier if you are using VFP's RI
>as VFP's RI doesn't handle compound indexes too well
>thus in a table that came from a relation (for example a many-to-many link
>table)
>instead of using a primary key which is tableA primary+tableB primary (which
>will cause you trouble with VFP RI)
>you would use a single surrogate key as the primary of the link table etc.
>
>
>Arnon

I'm not sure about this either; in the many-to-many link I would need to use, instead of KEY_A+KEY_B, SURROGATE_A+SURROGATE_B; the problem with compound indexes would remain. Anyway, you would need to define KEY_A+KEY_B as CANDIDATE, and the original problem is still with us.

I have been showing my example code to many FoxPro programmers, and they all called it a serious problem. When I worked with old fashioned things like COBOL, if I deleted a primary key, it stayed deleted! (It didn´t show up in READs, it caused no problems in WRITEs, and so on.)

The only (arrrrgghh!) solution I have found so far is using a REGULAR index and checking for duplicates in the INSERT trigger (which I write, instead of using the Foxpro generated ones).