>Is it better to use an "automatically incrementing" numeric field as the primary key. In this case, the company we get the data from recycles the key field [an insurance policy number] -- I don't know how they do it. Sometimes we need to reuse the policy id after it has been deleted [a policy gets "reissued"], but we can't reuse the policy id if it currently exists -- don't want duplicate policy numbers. Is it better to just seek that record to see if it exists before adding the new record rather than worrying about making it the primary key?
>

Nope - add a surrogate key that uniquely identifies the record but doesn't carry any information in and of itself. That way, if the policy number is changed or reassigned, the record's primary key remains constant. A primary key should uniquely identify the record at all times, regardless of the record's status, otherwise, the relationships between tables can get confued.

You can obviously seek a record to see if it already exists. The real problem here comes from possible sequence of event errors, since the policy might not have been processed for cancellation before reissue.

>Thanks,
>Doug