Mike,
My responses in context below
>In the past I have added pre-insert/update code in FP to check for duplicates.
>How do you handle this?
If I understand correctly that you are talking about a 'candidate' business key such as th "ID Code" I talked about, I do use a "pre-insert" type hook to check this. As you pointed out, you could use a candidate index if you need to handle it at the database level.
>Also, how would you handle the extreme situation where possibly this key field became corrupted somehow. How could you rebuild the FK relation if the business data was not used as the PK of the other table. Guess it would be a tough situation either way.
Well - if your PK field becomes corrupted, you are pretty well sunk either way unless it is a denormalized field built from other fields that somehow avoided corruption in this process. As far as FKeys - you are equally sunk either way since the FKEY can't have any business meaning in the child table in either case.
Only real downside I see to surrogate keys is the extra space they take up if you have a naturally occuring key already in the table. At 4 Bytes a record, I can live with it.
Ken
Ken B. Matson
GCom2 Solutions