为什么连接表上有主键不好？
我正在观看一个截屏视频，其中作者说在连接表上有主键不好，但没有解释原因。
示例中的联接表在 Rails 迁移中定义了两列，作者为每列添加了索引，但没有主键。
为什么在这个例子中没有主键是不好的？
create_table :categories_posts, :id => false do |t|
t.column :category_id, :integer, :null => false
t.column :post_id, :integer, :null => false
end
add_index :categories_posts, :category_id
add_index :categories_posts, :post_id
编辑：正如我向 Cletus 提到的，我可以理解自动编号字段作为主键的潜在用处，即使对于连接表也是如此。 然而，在上面列出的示例中，作者明确避免在“create table”语句中使用语法“:id => false”创建自动编号字段。 通常，Rails 会自动将自动编号 id 字段添加到在这样的迁移中创建的表中，这将成为主键。 但是对于这个join表，作者特意阻止了。 我不确定他为什么决定采用这种方法。
I was watching a screencast where the author said it is not good to have a primary key on a join table but didn't explain why.
The join table in the example had two columns defined in a Rails migration and the author added an index to each of the columns but no primary key.
Why is it not good to have a primary key in this example?
create_table :categories_posts, :id => false do |t|
t.column :category_id, :integer, :null => false
t.column :post_id, :integer, :null => false
end
add_index :categories_posts, :category_id
add_index :categories_posts, :post_id
EDIT: As I mentioned to Cletus, I can understand the potential usefulness of an auto number field as a primary key even for a join table. However in the example I listed above, the author explicitly avoids creating an auto number field with the syntax ":id => false" in the "create table" statement. Normally Rails would automatically add an auto-number id field to a table created in a migration like this and this would become the primary key. But for this join table, the author specifically prevented it. I wasn't sure why he decided to follow this approach.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
一些注意事项：
Some notes:
在任何表上都没有主键是一个坏主意（如果 DBMS 是关系 DBMS - 或 SQL DBMS）。 主键是数据库完整性的重要组成部分。
我想如果你不介意你的数据库不准确并且经常提供不正确的答案，那么你可以不用......但大多数人都希望从他们的 DBMS 中得到准确的答案，对于这样的人来说，主键至关重要。
It is a bad idea not to have a primary key on any table, period (if the DBMS is a relational DBMS - or an SQL DBMS). Primary keys are a crucial part of the integrity of your database.
I suppose if you don't mind your database being inaccurate and providing incorrect answers every so often, then you could do without...but most people want accurate answers from their DBMS and for such people, primary keys are crucial.
DBA 会告诉您，这种情况下的主键实际上是两个 FK 列的组合。 由于 Rails/ActiveRecord 不能很好地处理复合 PK（至少在默认情况下），这可能就是原因。
A DBA would tell you that the primary key in this case is actually the combination of the two FK columns. Since Rails/ActiveRecord doesn't play nice with composite PKs (by default, at least), that may be the reason.
外键的组合可以为主键（称为复合主键）。 就我个人而言，我更喜欢使用技术主键而不是（自动编号字段、序列等）。 为什么？ 嗯，它使得识别记录变得容易容易，如果您要删除它，您可能需要这样做。
想一想：如果您要呈现一个包含所有链接的网页，那么使用主键来标识记录会使事情变得更加容易。
The combination of foreign keys can be a primary key (called a composite primary key). Personally I favour using a technical primary key instead of that (auto number field, sequence, etc). Why? Well, it makes it much easier to identify the record, which you may need to do if you're going to delete it.
Think about it: if you're going to present a Webpage of all the linkages, having a primary key to identify the record makes it much easier.
基本上是因为没有必要。 两个外键字段的组合足以唯一地标识任何行。
但这仅仅说明了为什么它不是一个好主意……但为什么它会是一个坏主意呢？
考虑添加标识列会增加的开销。 该表将多占用 50% 的磁盘空间。 更糟糕的是指数情况。 对于身份字段，您必须维护身份计数以及第二个索引。 您的磁盘空间将增加两倍，每次插入所需执行的工作量将增加两倍。 唯一的优点是 DELETE 命令中的 WHERE 子句稍微短一些。
另一方面，如果复合键字段是整个表，那么索引可以是表。
Basically because there's no need for it. The combination of the two foreign key field adequately uniquely identifies any row.
But that merely says why it's not a Good Idea.... but why would it be a Bad Idea?
Consider the overhead adding a identity column would add. The table would take up 50% more disk space. Worse is the index situation. With a identity field, you have to maintain the identity count, plus a second index. You'll be tripling the disk space and tripling the work the needs to be performed on every insert. With the only advantage being a slightly shorter WHERE clause in a DELETE command.
On the other hand, If the composite key fields are the entire table, then the index can be the table.
将最具选择性的列放在前面应该只与 INDEX 声明相关。 在 KEY 声明中，这应该无关紧要（因为，正如已经正确指出的那样，KEY 是一个 SET，并且在集合内部，顺序并不重要 - 集合 {a1,a2} 与 {a2} 是相同的集合,a1})。
如果 DBMS 产品的 KEY 声明中的属性顺序会产生影响，那么该 DBMS 产品就会犯下错误：未能正确地区分数据库的逻辑设计（执行 KEY 声明的部分）和数据库的物理设计。数据库（进行 INDEX 声明的部分）。
Placing the most selective column first should only be relevant in the INDEX declaration. In the KEY declaration, it should not matter (because, as has been correctly pointed out, the KEY is a SET, and inside a set, order doesn't matter - the set {a1,a2} is the same set as {a2,a1}).
If a DBMS product is such that ordering of attributes inside a KEY declaration makes a difference, then that DBMS product is guilty of not properly distinguishing between the logical design of a database (the part where you do the KEY declaration) and the physical design of the database (the part where you do the INDEX declaration).
我想对以下评论发表评论：“说零个或更多是不正确的”。
我想指出的是，添加此评论的文本根本不包含文本“零或更多”，因此我想评论的评论的作者正在批评其他人没有说过的事情。
我还想评论说，说“零个或更多”是不正确的，这是不正确的。 今天，关系理论在少数仍然费心研究该理论细节的人中广为人知，实际上需要没有属性的密钥的可能性。
但是当我按下“评论”按钮时，系统回复我说评论需要50分（或类似的声誉分数）。
这是一个悲伤的例子，说明世界似乎忘记了科学不是民主，在科学中，真理并不取决于谁碰巧是多数，也不是取决于谁碰巧拥有“足够的声誉”。
I wanted to comment on the following comment : "It is not correct to say zero or more".
I wanted to remark that the text to which this comment was added simply did not contain the text "zero or more", so the author of the comment I wanted to comment on was criticizing someone else for something that hadn't been said.
I also wanted to comment that it is not correct to say that it is not correct say "zero or more". Relational theory as commonly known today among the few people who still bother to study the details of that theory, actually REQUIRES the possibility of a key with no attributes.
But when I pressed the button "comment", the system responded to me that commenting requires a reputation score of 50 (or some such).
A sad illustration of how the world seems to have forgotten that science is not democracy, and that in science, the truth is not determined by whoever happens to be the majority, nor by whoever happens to have "enough reputation".
拥有单个 PK 的优点
使用单个 PK 的缺点
注释
Pros of having a single PK
Cons of having a single PK
Notes