>>How do I deal with the PK issue, well I ONLY use surrogate PKs, that is fields that have absolutely no meaning other than being the PK for the table. That way I NEVER reuse a PK value so I don't care if the deleted records are there or not.
>
>Hear, hear, Jim. I never cease to wonder why on Earth people use data-bearing keys. It's just to much of a PITA to manage and there is absolutely little or no need.

As much as possible, I use surrogate PKs. However, I do have a requirement for data-bearing keys in some tables...let me describe.

I designed an application which keeps track of configuration of telecommunication circuits (T1 based, ATM based, etc). A table contains the Circuit ID (similar to a serial no, which I use as PK) of each circuit component. Without going into details, other tables contain the orders placed against these circuits, various component specifications and others act as links between the component tables to form the entire logical circuit.

Since Circuit IDs are determined by the carriers, we don't know what they will be at the time the order is placed. Later, when we receive details on the circuit specifications, we need to update the Circuit ID. Since this Circuit ID may show up in many tables (because of links to other components, order and cost records) I use Referential Integrity to cascade the updates of the PK(Circuit ID) from the main Circuit table to all other related tables.

By doing this, I save a lot of coding and the update is done it one place only.

I wouldn't mind hearing your comments if you disagree with such usage of PKs, but if you do, please comment on why this wouldn't be considered a good practice.