Can we update primary key values of a table?

Question

Can we update primary key values of a table?

Answer 1

It is commonly agreed that primary keys should be immutable (or as stable as possible since immutability can not be enforced in the DB). While there is nothing that will prevent you from updating a primary key (except integrity constraint), it may not be a good idea:

From a performance point of view:

• You will need to update all foreign keys that reference the updated key. A single update can lead to the update of potentially lots of tables/rows.
• If the foreign keys are unindexed (!!) you will have to maintain a lock on the children table to ensure integrity. Oracle will only hold the lock for a short time but still, this is scary.
• If your foreign keys are indexed (as they should be), the update will lead to the update of the index (delete+insert in the index structure), this is generally more expensive than the actual update of the base table.
• In ORGANIZATION INDEX tables (in other RDBMS, see clustered primary key), the rows are physically sorted by the primary key. A logical update will result in a physical delete+insert (more expensive)

Other considerations:

• If this key is referenced in any external system (application cache, another DB, export...), the reference will be broken upon update.
• additionaly, some RDBMS don't support CASCADE UPDATE, in particular Oracle.

In conclusion, during design, it is generally safer to use a surrogate key in lieu of a natural primary key that is supposed not to change -- but may eventually need to be updated because of changed requirements or even data entry error.

If you absolutely have to update a primary key with children table, see this post by Tom Kyte for a solution.