Can we update primary key values of a table?

Question

Can we update primary key values of a table?

Answer 1

Short answer: yes you can. Of course you'll have to make sure that the new value doesn't match any existing value and other constraints are satisfied (duh).

What exactly are you trying to do?

Answer 2

You can as long as

• The value is unique
• No existing foreign keys are violated

Answer 3

You can, under certain circumstances.

But the fact that you consider this is a strong sign that there is something wrong with your architecture: Primary keys should be pure technical and carry no business meaning whatsoever. So there should never be the need to change them.

Thomas

Answer 4

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 to update 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 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.

Answer 5

Primary key attributes are just as updateable as any other attributes of a table. Immutability is often a desirable property of a key but definitely not an absolute requirement. If it makes sense from a business perpective to update a key then there's no fundamental reason why you shouldn't.