50

Can we update primary key values of a table?

Marco Staffoli
  • 2,475
  • 2
  • 27
  • 29
akp
  • 1,823
  • 7
  • 26
  • 29

6 Answers6

51

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.

Community
  • 1
  • 1
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • 6
    I'd say that stability is more commonly cited as a desirable attribute of a PK rather than immutability. [Examples](http://stackoverflow.com/questions/3632726/what-are-the-design-criteria-for-primary-keys/3668234#3668234) – Martin Smith Oct 01 '10 at 12:11
  • 2
    @Martin: I understand the distinction but I think a *physical* primary key (i.e. the one you define in the DB) should be immutable. The primary key of the *conceptual data model* should have the attributes given in the answers from the link you provided but may be different from the final physical primary key (familiarity for example is not IMO a criteria for the primary key of the actual table). My answer relates to the primary key of the physical model. – Vincent Malgrat Oct 01 '10 at 13:04
  • @Martin I agree with you. For example: a table "OrdersStatus". Usualy the PK of this table contains values like "SHIPPED" "INPROGRES" "CANCELLED"... In the application ther isn't a place where user can change the PK value, so we can say that the value is "stable". But some time in the future can be that in our application whe need to have 2 distinc SHIPPED status: "SHIPPED_TO_CUSTOMER", "SHIPPED_TO_SUPPLIER". – Marco Staffoli Aug 10 '12 at 07:26
  • I have a similar problem and in my case if I use surrogate instead of a natural key I would need an additional index (on the natural column) so in this case it will be far more efficient to update the natural key (later when its known later). – bodrin Jan 08 '16 at 14:43
  • 1
    If it would be a general konsensus, that primary keys should be immutable, then all DBMS would prohibit updates on primary keys. but what with deleting the dataset and inserting it again with the modified primary key value? a primary key's attributes are "integrity" and "uniqueness", but not "immutability" – emfi Feb 19 '18 at 09:21
17

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.

nvogel
  • 24,981
  • 1
  • 44
  • 82
8

You can as long as

  • The value is unique
  • No existing foreign keys are violated
Richard
  • 840
  • 6
  • 26
6

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.

Beto Boullosa
  • 83
  • 1
  • 5
2

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?

Manoj Govindan
  • 72,339
  • 21
  • 134
  • 141
  • My guess would be merging database 2 into database 1, where each has a user table, and in those 2 tables, the same username has a different id (autoincrement id). – Stefan Steiger Jun 13 '13 at 06:08
1

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

Thomas Weller
  • 11,631
  • 3
  • 26
  • 34
  • 5
    "Primary keys should be pure technical and carry no business meaning whatsoever" - that's your opinion, not a fact. Surrogate vs. natural debate has not been closed. – Tony Andrews Oct 01 '10 at 11:52
  • 1
    While I agree the debate is still open, this case is a strong argument for surrogates. – DCookie Oct 01 '10 at 14:18
  • If your composite primary key holds a foreign key to another table. you might want to change the foreign key component to point to some other record. – timothy Nov 05 '21 at 03:38