所有数据库表都应该有主键吗？
给每个数据库表一个主键是一个好习惯吗？在我看来，如果没有明确需要主键，那么我的数据库中只会变得更加混乱。
Is it good practice to give every database table a primary key? It seems to me that if the primary key is not explicitly needed, then it would just be extra clutter in my database.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
当您可能会：
在 OLTP 数据库中，您几乎总是（在我的例子中总是）有某种类型的主键。有时是 Guid，有时是自动编号/身份字段，有时是由应用程序或客户设置。有时甚至是多个字段的组合。这是因为您通常希望唯一地标识表中的任何给定行。
此外，主键是查询优化器使用的约束，可以提高查找和连接的性能。
当您可能不会时：
您唯一没有主键的情况是在“报告”表中，可能是在非规范化数据仓库中。
When you probably WOULD:
In an OLTP database you'd almost always (in my case always) have a primary key of some sort. Sometimes Guid, sometimes autonumber/identity fields, sometimes set by the application or the customer. Sometimes even a combination of more than one field. This is because you'll typically want to uniquely identify any given row from the table.
Also, a primary key is a constraint used by the query optimiser that should improve performance for lookups and joins.
When you probably WOULDN'T:
The only time you wouldn't have a primary key is in a "reporting" table, probably in a denormalised data warehouse.
是的，在每个表上都有一个主键是一个很好的做法。
但是，并非每个表都应该有一个自动编号 id 列。我觉得有必要把这一点说清楚，因为出于某种原因，很多人倾向于在所有表中添加一个额外的 ID，即使已经存在一个非常好的候选者。例如，表示
Users<->的多对多表。群组应使用{user_id, group_id}。除了在门口阻止重复之外，主键约束还携带优化器在生成执行计划时使用的信息。
这就是为什么我总是（或者至少除了极少数例外）在我创建的所有表上都有一个主键。
事实上，我什至在报告表上创建主键，其中大多数列都是主键的一部分。因为在开发过程中，我会因为做错了什么而至少遇到一次唯一约束违规。有了大量的数据并且没有任何限制，我就不会发现这个错误。
Yes, it is good practise to have a primary key on every table.
But, NOT every table should have a single auto number id column. I felt the need to spell that out, because for some reason lots of people tend to throw in an extra ID in all tables even though a perfectly good candidate already exist. For example, a many-to-many table representing
Users <-> Groupsshould use{user_id, group_id}.Apart from stopping duplicates at the door, a primary key constraint also carries information which is used by the optimizer when generating execution plans.
This is why I always, or at least with very few exceptions, have a primary key on all tables I create.
In fact, I even create primary keys on reporting tables where most of the columns are part of the primary key. Because during development, I will get at least one unique constraint violation because I did something wrong. With shitloads of data and no constraint in place I wouldn't have spotted the error.
这不是必需的，但请确保您永远不需要它。主键的目的是让您可以根据 ( (通常是最小的）标准集。例如，这允许数据库确保您没有重复的数据，这还允许您的数据库符合 第一范式。如果这不是必需的，那么您不需要主键，但首先要仔细考虑一下。
不要忘记，主键不一定是包含任意唯一值的附加列 - 它也可以是一组共同定义唯一性的列（例如，一个人的名字、姓氏和日期）出生在地址簿表中）。
It's not required, but be sure that you don't ever need one. The purpose of the primary key is so that you can uniquely identify one row from another based on a (usually minimal) set of criteria. This allows the database to ensure that you don't have duplicate data, for example, which also allows your database to conform to 1st normal form. If this isn't required then you don't need a primary key, but think carefully about it first.
Don't forget that the primary key doesn't necessarily have to be an additional column that contains an arbitrary unique value - it can also be a set of columns which together define uniqueness (e.g., a person's first name, last name and date of birth in an address book table).
关系数据库设计中键的目的是防止重复数据，从而帮助确保数据完整性。如果允许重复的行，那么就会产生歧义、潜在的更新异常以及很可能不正确的结果。因此，一般来说，每个表都应该至少有一个键（如果需要，可以有多个键）。通常情况下，“没有明确需要”数据完整性！
主键只是表中的任意一个键。将一个键指定为主键可能很有用，但并不是特别重要 - 重要的是每个表至少有一个候选键。
数据库文献中广泛介绍了应避免重复行的原因。请参阅：
http://www.dbdebunk.com/page/page/627052.htm< /a>
http://www.dbdebunk.com/page/page/638922.htm
http://dl.acm.org/itation.cfm?id= 77708
http://www.amazon.com/Practical-Issues-Database -管理-从业者/dp/0201485559
The purpose of keys in relational database design is to prevent duplicate data and thereby help ensure data integrity. If you permit duplicate rows then you create ambiguity, potential update anomalies and very possibly incorrect results. Therefore in general every table ought to have at least one key (and maybe more than one if required). It's not usually the case that data integrity is "not explicitly needed"!
A primary key is just any one of the keys of a table. Designating one key as a primary key can be useful but is not particularly important - the important thing is that each table have at least one candidate key.
The reasons why duplicate rows ought to be avoided are covered pretty extensively in database literature. See:
http://www.dbdebunk.com/page/page/627052.htm
http://www.dbdebunk.com/page/page/638922.htm
http://dl.acm.org/citation.cfm?id=77708
http://www.amazon.com/Practical-Issues-Database-Management-Practitioner/dp/0201485559
一般来说是的 - 我会对那些只是为了报告目的而存储的“真实”数据的汇总版本的表进行例外（即为报告/性能原因创建的汇总表），但通常我总是有一个主键 - 并且在我的应用程序中，它几乎总是一个自动递增整数，相对于行大小几乎不占用额外空间。
Generally yes - I'd make exceptions for tables that are simply rolled up versions of 'real' data stored for reporting purposes (i.e. rollup tables created for reporting/performance reasons), but generally I always have a primary key - and in my apps, its almost always an auto-increment integer that takes almost no extra space relative to the row size.
虽然主键非常有用（我一直使用它们），但我们要明确的是，如果您不需要主键，则无需创建主键。在某些情况下，您不需要一个表，其中：
在上述两种情况下，您可能纯粹对有关表的聚合信息感兴趣，而不是对唯一标识行感兴趣。我相信还有其他人。但不使用主键也可以——这就是为什么在创建表时不需要主键（在大多数系统中）。
While primary keys are hugely useful (and I use them all the time), let's be clear that there's no need to create a primary key if you don't need one. There are cases where you don't need one, among them:
In both cases above, you may be interested purely in aggregate info about a table and not in identifying a row uniquely. I believe there are others. But not using primary keys are fine -- this is why they are not required when you create a table (in most systems).
我有一个按 CreateDate 分区的表，该表不是唯一的。就我而言，我决定从此表中删除主键，因为主键索引必须是唯一的这一事实意味着我无法将此索引设为本地索引，而必须是全局索引。这意味着从该表中进行的任何删除（以及其他操作）都会使该主键索引无法使用，从而使其变得无用。
I have a table that is partitioned by CreateDate which is not unique. In my case I have decided to remove the primary key from this table because the fact that a primary key index must be unique meant that I couldn't make this index a LOCAL index, instead it has to be GLOBAL. This would have meant that any deletion from that table (amongst other actions) would have made this primary key index unusable, rendering it useless.