为什么单主键比复合键好? [英] Why single primary key is better than composite keys?

问题描述

为什么拒绝复合键而支持使用名为 id 的单个主键的所有表?因为一般所有的 ORM 都遵循这个.

Why is the rejection of composite keys in favor of all tables using a single primary key named id? Cause generally all ORM follow this.

编辑

我刚开始在 Rails 上学习 ruby​​，在 pragmatic 的敏捷开发书中有一句话:---除非每个表都有一个数字主键，否则 Rails 真的不能很好地工作.对列的名称不那么挑剔.我在学习 Doctrine 时读过的同样的台词.

I just started learning ruby on rails and in the book of agile development by pragmatic there is a line:--- Rails really don't work too well unless each table has a numeric primary key. It is less fussy about the name of the column. Same kind of line I read when I was learning Doctrine.

EDIT2也请检查此链接.我对这件事越来越困惑:---复合主键与唯一对象 ID 字段

EDIT2 Please check this link too. I am getting more and more confused about this thing:--- Composite primary keys versus unique object ID field

来自上面的链接:--

*主键应该是常量和无意义的；非代理键通常无法满足一项或两项要求，最终

*the primary key should be constant and meaningless; non-surrogate keys usually fail one or both requirements, eventually

如果密钥不是恒定的，则您有一个可能会变得非常复杂的未来更新问题如果密钥不是无意义的，那么它更有可能改变，即不是恒定的；见上

If the key is not constant, you have a future update issue that can get quite complicated if the key is not meaningless, then it is more likely to change, i.e. not be constant; see above

举一个简单、常见的例子:一个库存项目表.将商品编号(SKU 编号、条形码、零件代码或其他任何内容)设为主键可能很诱人，但一年后所有商品编号都发生了变化，您将面临非常混乱的整体更新——数据库问题...

Take a simple, common example: a table of Inventory items. It may be tempting to make the item number (SKU number, barcode, part code, or whatever) the primary key, but then a year later all the item numbers change and you're left with a very messy update-the-whole-database problem...

还有一个比哲学更实用的问题.在许多情况下，您会以某种方式找到特定行，然后更新它或再次找到它(或两者兼而有之).使用复合键，有更多的数据需要跟踪，并且 WHERE 子句中有更多的约束来重新查找或更新(或删除).也有可能其中一个关键段可能在此期间发生了变化！.使用代理键时，始终只保留一个值(代理 ID)，并且根据定义，它不能更改，这大大简化了情况.*

there's an additional issue that is more practical than philosophical. In many cases you're going to find a particular row somehow, then later update it or find it again (or both). With composite keys, there is more data to keep track of and more constraints in the WHERE clause for the re-find or update (or delete). It is also possible that one of the key segments may have changed in the meantime!. With a surrogate key, there is always only one value to retain (the surrogate ID) and by definition, it cannot change, which simplifies the situation significantly.*

推荐答案

我不认为有一个笼统的声明，你应该只使用一个名为 id 的主键.

I don't think there is a blanket statement that you should only ever use a single primary key named id.

大多数人使用代理主键作为自动生成的 int，因为它将主键隔离在需要更改的地方，例如如果您将 PK 设为用户名，然后他们更改了他们的合法名称.您必须更新 PK 和所有 FK 列以反映新名称.如果您使用了代理主键，则只需在一处更新用户名(因为表连接的是 int 而不是名称).

Most people use a surrogate primary key as an auto generate int, because it isolates the primary key from ever needing to be changed, like if you make the PK the user name and they later changed their legal name. You would have to update the PK and all FK columns to reflect the new name. if you had used a surrogate primary key, you just update the user's name in one spot (because the tables join on the int not the name).

主键的大小很重要，因为 PK 会复制到您在表上构建的每个索引中.如果 PK 很大(如字符串)，则索引中每页的键较少，索引将占用更多缓存内存来存储它.整数很小.

The size of a primary key is important because the PK is duplicated into every index you build on the table. If the PK is large (like a string) you have fewer keys per page in the index and the index will take more cache memory to store it. Ints are small.

具有自动增量 int PK 使其本身成为一个聚集索引，因为行按此顺序存储，并且无需返回并颠倒行以插入新行，您总是添加到桌子的尽头.

Having a auto increment int PK lends itself to being a clustered index well, as rows are stored in this order and there is no need to go back and bump rows out of the way to insert a new row, you always add to the table's end.

这篇关于为什么单主键比复合键好?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！