30

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 ?

Improve this question

9 Answers 9

Reset to default
28

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.

Improve this answer
6
  • 8
    Why do you "need the primary key to be immutable if it's linked to a foreign key"? As the OP mentioned, most RDBMSs have the "cascade" update feature.
    – Thanatos
    Nov 17, 2011 at 9:25
  • 1
    @Thanatos most (in fact all I've encountered) rdbms's will not allow mutable primary keys yet have cascading updates. A primary key, in generally accepted dba wisdom, should contain no information, be only a unique record identifier (so not even a timestamp, record range deferred from it, etc.).
    – jwenting
    Nov 17, 2011 at 11:32
  • 6
    @jwenting: Are we talking about the same thing? "most rdbms's will not allow mutable primary keys" includes what? MySQL and PostgreSQL both allow mutable primary keys, and honor cascading updates… as I think standard SQL says they should. Also, "generally accepted dba wisdom"? I've met plenty of DBAs who argue against surrogate keys, and plenty who argue against natural keys.
    – Thanatos
    Nov 18, 2011 at 1:29
  • 2
    @Thanatos Arguments in favor of "all tables must have surrogate" lack bibliographic references, they cite "generally accepted dba wisdom" but they never cite a book. Canonical books say you should use surrogate if: A. no natural key exist, B. multicolumn key is excedes 3 columns, or, C. You will be changing the key all the time. So: natural when they fit, surrogates when naturals won't fit.
    – Tulains Córdova
    Jun 4, 2013 at 16:48
  • 4
    @user61852: What?
    – Guffa
    Jun 4, 2013 at 18:01
16

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.

Improve this answer
16

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.

Improve this answer
3
  • Good point about data-entry errors
    – Tim Goodman
    Sep 29, 2010 at 19:12
  • 1
    richeym, seems like you are making the case why keys must NOT be immutable: users may want to change them.
    – nvogel
    Oct 1, 2010 at 14:43
  • 4
    @dportas - my point is that I like PK's to be immutable so always use surrogate keys even if I think there is an obvious key that can be derived from the table data (e.g. email, username).
    – richeym
    Oct 9, 2010 at 8:43
2

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

Improve this answer
2

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.

Improve this answer
1
  • 2
    "keys are for computers, let the people screw up the rest of the data." +1, nice.
    – user82096
    Jun 4, 2013 at 14:53
2

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 of 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 accepted 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 lack, e.g. familiarity (everyone in the book trade knows or 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.

Improve this answer
2
  • 3
    ISBN is fixed width, except when it's not (see ISBN-10 vs ISBN-13).
    – user
    Nov 17, 2011 at 10:53
  • So how would you recommend handling duplicate ISBNs? In all RDBMSes I am aware of, you would have to have a UNIQUE constraint on the field to use it as a primary key.
    – Wildcard
    Aug 15, 2016 at 23:20
1

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

Improve this answer
1

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.

Improve this answer
0

As some comments already said it, one solution is to use a new primary key

For example (following the example of @onedaywhen), let's say that exists the table Books that store a list of books and we "used" to determine the ISBN as the primary key. However, some authors committed the mistake of typed a wrong ISBN so, they asked for change the ISBN, it involved the next tasks:

  • create a new registry in the table Books
  • point all references from the old ISBN to the new ISBN. (*)
  • And finally, delete the old registry from the table Books.

(*) this could be trivial to find all the references for a database model that uses Foreign Keys but some models lacks of it.

Table Books
ISBN  is the primary key
NAME is a simple field.
etc.

We change it as

Table Books
InternalBookId as the primary key
ISBN as a simple field or an indexed field.
NAME is a simple field.
etc.

Where the new InternalBookId could even been a autonumeric value.

The cons about it:

  • it adds a new field that uses more space / resource.

  • it could requires to rewrite the entire model.

  • the new model could be less self explained.

The pro

  • Allows to mutate the "primary key".
  • Allows to even drop or refactoring the "primary key", for example, to change Books to ISBN-13 is so simple as to drop the older column and to create a new one

New table:

Table Books
InternalBookId as the primary key
ISBN13 is a new field.
NAME is a simple field.
etc.
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.