为什么单键主键优于复合键？ [英] Why single primary key is better than composite keys?
问题描述
为什么使用名为id的单个主键拒绝复合键有利于所有表。因为一般所有的ORM都遵循这个..？
编辑
刚刚开始在轨道上学习ruby，而在敏捷开朗的书中，实际上有一行：---
除非每个表都有一个数字的主键，否则Rails真的不会很好。对列的名称不那么吝啬。
当我学习教条时，我读到的行类似。
EDIT2
请检查此链接。我越来越困惑这个事情：---
复合主键与唯一对象ID字段
从以上链接： -
如果键不是常数，则有未来的更新问题可能会相当复杂
if关键是没有意义的，那么它更有可能改变，即不是恒定的;见上文
采取简单的常见示例：库存项目表。将项目编号（sku号，条形码，部件代码或其他）作为主键可能很诱人，但是一年后，所有的项目编号都会更改，而且您会留下一个非常混乱的更新 - 数据库问题...
编辑：还有一个比哲学更实际的问题。在许多情况下，您将要找到一个特定的行，然后更新它或再次找到（或两者）。使用复合键有更多的数据可以跟踪和更多的依赖WHERE子句重新找到或更新（或删除）。同时有可能其中一个关键细分可能发生变化！使用代理键，总是只保留一个值（代理ID），根据定义，它不能改变，这样可以显着简化情况。*
我不认为有一个全面的陈述，你应该只使用一个名为id的主键。
大多数人使用代理主键作为自动生成int，因为它将主键隔离，无需更改，就像您将PK设为用户名，稍后更改其法定名称。您将必须更新PK和所有FK列以反映新名称。如果您使用了代理主键，则只需在一个位置更新用户的名称（因为表中的表不加上名称）。
主键是重要的，因为PK被复制到您在表上构建的每个索引。如果PK很大（像一个字符串），索引中的每个页面的键数就会减少，索引将会占用更多的缓存来存储它。 Ints很小。
拥有自动递增int PK可以很好地将其作为聚簇索引，因为行按此顺序存储，并且不需要返回碰撞行排除插入新行的方式，总是添加到表的结尾。
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..???
EDIT
I just started learning ruby on rails and in the book of agile develepment by pragmatic there is a line:--- Rails really doesn’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 Please check this link too. I am getting more and more confused about this thing:--- Composite primary keys versus unique object ID field
From the above link:--
*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
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...
EDIT: 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 contraints 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.*
I don't think there is a blanket statement that you should only ever use a single primary key named id.
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).
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.
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屋！
