Hi Juan,
If you want to enforce a unique value for SSN, there are two ways to do it. Make it a candidate key and do some error trapping because you WILL get an error if you try to update a duplicate value, or trap it logically using a SEEK to look for the value before updating the table and make it any type of key you want.
Personally, I'm not in love with candidate keys. I generally have a non-data bearing primary key using a GUID or something guaranteed to be unique and make everything else regular. If I need to trap uniqueness, I so a SEEK before saving. There is a difference between enforcing uniqueness in business logic and enforcing uniqueness via domain using candidates. The difference is, if you enforce it with candidate keys, you will get errors that you have to trap via an error handler. Simpler to avoid them via business logic.
>One solution to avoid duplicate keys when you delete a record and add a new one with the same ‘business key value’ is to use surrogate keys (integer values).
>
>But, if i want to enforce unique values for my business key (Ex: social security number) using candidate indexes, i find that problem again: Duplicate keys.
>
>Any workarounds? I have been updating ‘business key fields’ with negative random values after deleting the record. It works, but it’s a strange trick, isn’t it?
>
>How do you solve this?
>
>Thanks,
>
>
>Juan Carlos Jaramillo.
------------------------------------------------
John Koziol, ex-MVP, ex-MS, ex-FoxTeam. Just call me "X"
"When the going gets weird, the weird turn pro" - Hunter Thompson (Gonzo) RIP 2/19/05