database 我们可以更新表的主键值吗？

Can we update primary key values of a table?

提问by akp

Can we update primary key values of a table?

我们可以更新表的主键值吗？

回答by Vincent Malgrat

It is commonly agreed that primary keys should be immutable(or as stable as possiblesince 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)

• 您将需要更新所有引用更新键的外键。单个更新可能会导致更新潜在的大量表/行。
• 如果外键未编入索引 (!!)，您将必须在子表上保持锁定以确保完整性。Oracle 只会持有锁很短的时间，但这仍然很可怕。
• 如果你的外键被索引（它们应该是），更新将导致索引的更新（在索引结构中删除+插入），这通常比基表的实际更新更昂贵。
• 在 ORGANIZATION INDEX 表中（在其他 RDBMS 中，请参阅集群主键），行按主键物理排序。逻辑更新将导致物理删除+插入（更昂贵）

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.

• 如果在任何外部系统（应用程序缓存、另一个数据库、导出...）中引用了此键，则该引用将在更新时被破坏。
• 此外，某些 RDBMS 不支持 CASCADE UPDATE，尤其是 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.

如果您绝对必须使用子表更新主键，请参阅Tom Kyte 的这篇文章以获取解决方案。

回答by nvogel

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.

主键属性与表的任何其他属性一样可更新。稳定性通常是键的理想属性，但绝对不是绝对要求。如果从业务的角度来看更新密钥是有意义的，那么您就没有根本的理由不应该这样做。

回答by Richard

You can as long as

你可以只要

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

• 价值是独一无二的
• 没有违反现有的外键

回答by Beto Boullosa

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.

从关系数据库理论的角度来看，更新表的主键绝对没有问题，前提是主键之间没有重复，并且您不尝试在任何主键中放置 NULL 值关键列。

回答by Manoj Govindan

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?

你到底想做什么？

回答by Thomas Weller

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

托马斯