>Hi Trey,
>Thanks for the input. One question though, if the identity columns work reliably, why use compound data primary keys? IOW, why do both unique integer key for a propogated foreign key and keep a data primary key on the table? It sems to me that if the integer key is unreliable, your database is screwed and if it is reliable, not why not use it as the primary key on the table itself?
>
>Gary
>
You need both for both sides of data integrity - internal to the table, and referential.
Internally to the table, you need a constraint (either unique or PK) on real data in order to best prevent duplicates.
Referentially, you need a constraint (either unique or PK) on the identity column in order to have foreign key constraints that refer to it in other tables.
One of them should be the PK constraint, so the physical table has a PK - physically, it doesn't matter which.
As for duplicates, an identity column alone won't help.
e.g., say you have an Employee table and the logical primars key (i.e., uniquely identifying real data) is the SSN. However, SSN is considered too sensitive to propagate to FKs, so you also add an identity column. You'll need a unique constraint of some sort on SSN to best prevent dupes and a unique constraint of some sort on the identity column so FKs can to refer to it.
Insanity: Doing the same thing over and over and expecting different results.