>Hi all,
>
>I'm having a discussion with my boss and a fellow co-worker who is a dba about the usage of surrogate keys for primary keys in tables (he calls them "artificial keys").
>
>My argument is for using single-field sequence numbers as primary keys for every table and using single-field foreign keys in any child tables (which would also contain their own single-field primary keys). My reasoning is that these sequence key fields will never change because they're not part of natural data. I'm also arguing that this approach is more efficient for SQL joins, indexed seeks, etc.
>
>My colleagues on the other hand are saying that it's a mistake to use "artificial" keys because they aren't self documenting in the code. They even argue for composite keys to be used when multiple fields uniquely identify a record.
>
>For example, let's say I have a division table, a department table that is a child of the division table, and a work center table that is a child of the department table. My colleagues are saying that the keyfields should be designed in the following fashion:
>
>DIVISION TABLE
>Field 1: Division # or name
>Primary Key: Division # or name
>
>DEPARTMENT TABLE
>Field 1: Division # or name
>Field 2: Dept # or name
>Primary Key: Division # + Dept #
>
>WORK_CENTER TABLE
>Field 1: Division # or name
>Field 2: Dept # or name
>Field 3: Work Center # or name
>Primary Key: Division # + Dept # + Work Center #
>
>My suggestion would look like the following:
>
>DIVISION TABLE
>Field 1: PKDivision (Integer)
>Field 2: Division # or name
>
>DEPARTMENT TABLE
>Field 1: PKDepartment (Integer)
>Field 2: FKDivision (Integer)
>Field 3: Department # or name
>
>WORK_CENTER TABLE
>Field 1: PKWorkCenter (Integer)
>Field 2: FKDepartment (Integer)
>Field 3: Work Center # or name
>
>I always thought that the going rule of thumb is that surrogate (artificial or auto-incrementing) keys are the way to go. I'm curious to know what the going consensus is here on the UT?
>
>Thanks!
>
>-JT

Jeff,

Just my $.02, but if you want to stump them, ask them if Social Security numbers are unique. If they say "yes", I can cite at least 2 real-world examples where they weren't.

1) In a Medicare office where I once worked, SSN was the "primary" identifier. However, it was very common until recently for a widow who never had a "job", to never get a SSN. But since her husband's death, she was entitled to benefits. How did they do it? They added an alpha character to the end of "his" SSN. Ex: "234-56-7890A" If a man died leaving dependent children as well as a spouse, they each got a letter tacked on to identify them. You have proven 2 issues here: #1: SSN is not unique. #2 SSN is not always purely numeric.

2) Here in Texas, several years ago a local university was rewriting their payroll application. They keyed the entire thing on SSN. Rather than run parallel, they did a "cut over" one weekend. On Friday (payday) of the next week, the office was STORMED by a mob of agitated maintenance workers. Come to find out, as many as 20-22 men were "sharing" a SSN, and with the new system, only the first one got paid. At that point in time, many of the undocumented workers were "legal", due to some grandfather clauses. The entire payroll system had to be rewritten.

The only certainty is that, in the real world, nothing can be guaranteed unique. Only artifice can do that. Go armed with E. F. Codd and a couple of "war stories" and see if your collegues are still in denial.