[database] Can we update primary key values of a table?

Can we update primary key values of a table?

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?

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

You can as long as

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

From a relational database theory point of view, there should be absolutely no problem on updating the primary key of a table, provided that there are no duplicates among the primary keys and that you do not try to put a NULL value in any of the primary key columns.

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.

Primary key attributes are just as updateable as any other attributes of a table. Stability 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.