Are we able to update primary key values of the table?

It's generally agreed that primary keys should be immutable (or as stable as possible since immutability cannot be enforced within the DB). While there's nothing which will stop you to update a principal key (except integrity constraint), it might not be advisable:

From the performance perspective:

  • You will have to update all foreign secrets that reference the up-to-date key. Just one update can result in update of potentially plenty of tables/rows.
  • When the foreign secrets are unindexed (!!) you'll have to conserve a lock around the children table to make sure integrity. Oracle is only going to contain the lock for a short while but nonetheless, this really is frightening.
  • In case your foreign secrets are indexed (as they must be), the update will result in the update from the index (remove+place within the index structure), this really is generally more costly compared to actual update from the base table.
  • In ORGANIZATION INDEX tables (in other RDBMS, see clustered primary key), the rows are physically sorted through the primary key. May well update can lead to an actual remove+place (more costly)

Other factors:

  • If the secret is recommended in almost any exterior system (application cache, another DB, export...), the reference is going to be damaged upon update.
  • additionaly, some RDBMS don't support CASCADE UPDATE, in particular Oracle.

To conclude, throughout design, it's generally safer to utilize a surrogate key instead of an all natural primary key that's supposed to not change -- but may eventually have to be up-to-date due to transformed needs as well as data entry error.

Should you absolutely need to update a principal key with children table, see this post by Tom Kyte for a solution.

Short answer: you actually can. Obviously you will need to make certain the new value does not match any existing value along with other constraints are satisfied (duh).

Exactly what are you attempting to do?

You are able to as lengthy as

  • The worthiness is exclusive
  • No existing foreign secrets are violated

Primary key characteristics are simply as updateable every other characteristics of the table. Immutability is frequently an appealing property of the key but certainly no absolute requirement. If it seems sensible from the business perpective to update a vital plus there is no fundamental reason you should not.

You are able to, under certain conditions.

But because you consider this can be a strong sign that there's a problem together with your architecture: Primary secrets ought to be pure technical and carry no enterprise meaning whatsoever. So there will not be the necessity to change them.

Thomas