Primary key question

[Andrzejek]

In an article (suggested by one of TT members, great article BTW) the author states this about the primary key:

stability (choose a [primary] key that seldom changes)

Is that right?
I would think the statement should be “choose a primary key that NEVER changes”

What’s the use of a primary key that changes, even if it happens sometimes?
If that happens, all Foreign keys would have to be ‘adjusted’ (changed) as well.

Comments?

Have fun.

---- Andy

 

[MarkSweetland]

I tend to use the primary keys that never change. But then again, the definition of primary key is of a key that uniquely identifies a row. A composite key made up of two or more columns can uniquely identify a row, but the column data may change as long as the combination of the key columns remain unique. And, as you point out, foriegn keys would likewise need to be changed.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[SkipVought]

This might be helpful...

http://www.techrepublic.com/article/the-great-primary-key-debate/1045050

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Thargy]

I concur with the word NEVER, by design they should never change.

However, there are circumstances where this is required (e.g. when data from two different databases is to be merged), and that's when the use of a surrogate key is vital, as it enables keys to be changed without altering data.


Regards

T

 

[Olaf Doschke]

Quoting a bit more:

Fabian Pascal, in his book SQL and Relational Basics, notes that the decision should be based upon the principles of minimality (choose the fewest columns necessary), stability (choose a key that seldom changes), and simplicity/familiarity (choose a key that is both simple and familiar to users)

This also has it wrong to ask for a "familar to users" key. At least when you're from the surrogate key camp, because then you'd rather say users should never ever see the primary keys of any records, as they are really seperate from the net data, merely identifiers, nothing else.

Also look at [URL unfurl="true"]http://www.datraverse.com/technology/sql.php[/url]

Obviously Fabian Pascal is of the camp preferring natural over surrogate keys. That explains it, because you can't guarantee any natural key to be invariant. "If you cannot find an obvious natural key, reconsider your design" is another argument of that camp.

There is a bit of truth in that, because if multiple records only differ in their primary key and that key is surrogate, it means you have redundant data beside that surrogate key. Still there are many situations that still is valid, eg think of measurements, any measurements, which are in a certain range can produce redundant, yet still relevant data. You can also consider that argument of redundancy with surrogate keys and a good normalization, so this also is no argument against surrogate keys, it's just an argument to design your database differently.

Everything speaks for surrogate keys as the only relevant type of primary keys, today. I think there is few disagreement on that, and the article SkipVought points to is making that very clear.

Bye, Olaf.