Can we update primary key values of a table

Question

User · Answer

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

User · Answer

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

User · Answer

You can as long as   The value is unique  No existing foreign keys are violated

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to database

Examples related to oracle

Examples related to primary-key