Should a primary key be immutable?

Question

A recent question on stackoverflow provoked a discussion about the immutability of primary keys. I had thought that it was a kind of rule that primary keys should be immutable. If there is a chance that some day a primary key would be updated, I thought you should use a surrogate key. However it is not in the SQL standard and some RDBMS' "cascade update" feature allows a primary key to change.

So my question is: is it still a bad practice to have a primary key that may change ? What are the cons, if any, of having a mutable primary key ?

Answer 1

You only need the primary key to be immutable if it's linked to a foreign key, or if it's used as an identifier outside the database (for example in an URL pointing to a page for the item).

On the other hand, you only need to have a mutable key if it carries some information that might change. I always use a surrogate key if the record doesn't have a simple, immutable identifier that can be used as key.

Answer 2

A primary key should be comprised of whatever tuples are necessary to determine uniqueness. Whether the data can change or not is irrelevant. Only the uniqueness of the record matters. That is the conceptual design of the database.

When we move into the realm of implementation, then the safest thing to do is simply use a surrogate key.

Answer 3

Yes, in my opinion a primary key should be immutable.

Even if there are obvious candidate keys, I always use a surrogate key. In the few occasion's I haven't done this i've nearly always regretted it. And no matter how immutable you think the key is, you can't safeguard against data-entry errors - telling users that they can't edit that bit of information because it's a primary key doesn't wash sadly.

Answer 4

Caching mechanisms in between database and user will lose effectiveness if primary keys change.

Answer 5

Why not? Because you want to eliminate a column?

Just because the requirements call for three columns to be unique, doesn't mean it has to be the primary key. You may think that rule will last forever (Remember during that meeting when the pinhead department manager swore that would never change? You know, the one that just got fired.), but it won't.

I don't get paid for every cascade update I implement and a bonus if I code it myself.

The computer doesn't require any meaning for a key; IMHO, keys are for computers, let the people screw up the rest of the data.

Answer 6

Everything that can possibly be immutable should be. It helps to ensure correctness and helps when you want to make your application multithreaded.

Answer 7

Yes, a primary key must be immutable, along with being non-null and unique. However, I have yet to find a database that enforced the immutability of primary keys so you can probably go ahead and change their values if you really want to.

Answer 8

It is not bad practice to have a key whose values may change.

The properties of a good key include stability. Immutability is ideal but not a prerequisite. Introducing an artificial key for the sake of immutability is bad practice.

Take the example if the International Standard Book Number (ISBN). It is very stable but not immutable: sometime book publishers make mistakes and -- horror! -- duplicate ISBN numbers can occur. Does this mean that ISBN should not be rejects as a candidate key in a computerized database? Of course not. One of the advantages of ISBN is that it has a trusted source that will resolve problems for all users globally.

There are other properies of a good key ISBN has that a meaningless auto-incrementing integer key will lacks e.g. familiar (everyone in the book trade knows is familiar with ISBN), verifiable (the ISBN is printed on all modern books), can be validated with reference to the DBMS (ISBN is fixed width and includes a checksum), etc.