When would you ever need to change a primary key's value?

Question

Hello, I've been reading up on foreign keys and such for postgres and I noticed that it allows a cascading update for foreign keys.

Well, my question is, when would you need to update the primary key of a row?

Apparently this guy needs to http://www.oreillynet.com/onlamp/blog/2004/10/hey_sql_fans_check_out_foreign.html but I'm not quite understanding how it could ever be useful.

Edit: I see for natural primary keys, how this could be used. But what about technical primary keys? Ones that have no meaning and are almost always auto generated on insert?

Answer 1

You would need to do it if you chose your primary key as a natural key instead of a surrogate key, and then later found out that the user changed their surname, or that they wrote their SSN incorrectly on the application form.

Moral of story: don't use natural keys as primary keys.

Answer 2

You might need to do this if using a natural primary key (one that has an actual meaning in the problem domain). If the meaning changed, then you'd need to cascade the change.

I supposed a bad example of this would be a database of buildings on a school campus, with the building name as a primary key (don't do this at home). If the building is renamed to bribe honor a new donor, then the key would need to change.

Answer 3

Well... we have a lot of primary keys that are defined as a human readable code. Terrible idea, but not much choice in the matter.

It is very very handy to be able to fix that PK, and all dependent records, when someone realizes it is misspelled, or the meaning has changed.

Answer 4

I had to change my PK several times, when exposing my PK to a third party system. From time to time they called us asking to change the PKs, to fit the records in their database (from time to time due to tech problem, the synchronization between there two systems - fails).

After several times we just stopped exposing the PK and add a new column.

Answer 5

For a synthetic, meaningless primary key like an autoincrementing column there should (with a few exceptions) never be any reason to update the PK value. If the PK is a user-visible value you might have to update it (which is one of the many arguments in favour of synthetic keys). An example of this situation is an insurance policy number. In some cases the year is a part of the number, and may tick over on every renewal. In some data models the record is just updated in situ.

Where this happens you would be better off to use a synthetic key, so that other items are not dependent on the visible number.

One possible scenario where you would need to update a synthetic key is if you were merging two or more application databases together. In this case you may need to shift keys en masse to avoid collissions with the keys of records from the other source.

Answer 6

You may get in this situation if you use natural primary key.

Here's one very fresh example: in Croatia government changed tax identification numbers for both companies and individuals. New law was introduced with January 1st 2010.

Last year, I was consultant in several projects where companies were changing natural key (old tax number) to surrogate key in existing applications. Natural key seemed logical selection to original designers of those apps because it was defined by law. And then it changed.

Answer 7

For autogenerated keys, one example that I came across here (can't remember question) is if you need to merge two database tables together. In this case, you'll likely have duplicates unless your keys happened to be offset enough.