database 数据库表应该总是有主键吗？

Should a database table always have primary keys?

提问by

Should I always have a primary key in my database tables?

我的数据库表中应该总是有一个主键吗？

Let's take the SO tagging. You can see the tag in any revision, its likely to be in a tag_rev table with the postID and revision number. Would I need a PK for that?

让我们采用 SO 标记。您可以在任何修订版中看到该标签，它可能位于带有 postID 和修订号的 tag_rev 表中。我需要PK吗？

Also since it is in a rev table and not currently use the tags should be a blob of tagIDs instead of multiple entries of multiple post_id tagid pair?

另外，因为它在一个 rev 表中并且当前没有使用标签应该是一个 tagID 的 blob 而不是多个 post_id tagid 对的多个条目？

采纳答案by Rob

You should strive to have a primary key in any non-trivial table where you're likely to want to access (or update or delete) individual records by that key. Primary keys can consist of multiple columns, and formally speaking, will be the shortest available superkey; that is, the shortest available group of columns which, together, uniquely identify any row.

您应该努力在任何可能想要通过该键访问（或更新或删除）单个记录的非平凡表中拥有一个主键。主键可以由多列组成，正式地说，将是最短的可用超键；也就是说，最短的可用列组，它们一起唯一标识任何行。

I don't know what the Stack Overflow database schema looks like (and from some of the things I've read on Jeff's blog, I don't want to), but in the situation you describe, it's entirely possible there is a primary key across the post identifier, revision number and tag value; certainly, that would be the shortest (and only) superkey available.

我不知道 Stack Overflow 数据库架构是什么样的（从我在 Jeff 的博客上读到的一些内容来看，我不想知道），但是在您描述的情况下，完全有可能存在一个主要的跨帖子标识符、修订号和标签值的键；当然，这将是最短（也是唯一）可用的超级密钥。

With regards to your second point, while it may be reasonable to argue in favour of aggregating values in archive tables, it does go against the principle that each row/column intersection in a table ought to contain one single value. While it may slightly simplify development, there is no reason you can't keep to a normalised table with versioned metadata, even for something as trivial as tags.

关于你的第二点，虽然支持在存档表中聚合值可能是合理的，但它确实违背了表中的每一行/列交叉点都应该包含一个单一值的原则。虽然它可能会稍微简化开发，但没有理由不能保留带有版本化元数据的规范化表，即使是像标签这样微不足道的东西。

回答by Mehrdad Afshari

A tableshould have a primary key so that you could identify each row uniquely with it.

一个表应该有一个主键，这样你就可以用它唯一地标识每一行。

Technically, you canhave tables without a primary key, but you'll be breaking good database design rules.

从技术上讲，您可以拥有没有主键的表，但您将违反良好的数据库设计规则。

回答by Pete TerMaat

See this related question about whether an integerprimary key is required. One of the answers uses tagging as an example:

请参阅有关是否需要整数主键的相关问题。答案之一使用标记作为示例：

Are there any good reasons to have a database table without an integer primary key

有一个没有整数主键的数据库表有什么好的理由吗

For more discussion of tagging and keys, see this question:

有关标记和键的更多讨论，请参阅此问题：

Id for tags in tag systems

标签系统中标签的 ID

回答by James Black

I tend to agree that most tables should have a primary key. I can only think of two times where it doesn't make sense to do it.

我倾向于同意大多数表应该有一个主键。我只能想到两次没有意义的地方。

1. If you have a table that relates keys to other keys. For example, to relate a user_id to an answer_id, that table wouldn't need a primary key.
2. A logging table, whose only real purpose is to create an audit trail.

1. 如果您有一个将键与其他键相关联的表。例如，要将 user_id 与 answer_id 相关联，该表不需要主键。
2. 一个日志表，其唯一真正的目的是创建审计跟踪。

Basically, if you are writing a table that may ever need to be referenced in a foreign key relationship then a primary key is important, and if you can't be positive it won't be, then just add the PK. :)

基本上，如果您正在编写一个可能需要在外键关系中引用的表，那么主键很重要，如果您不能肯定它不会，那么只需添加 PK。:)

回答by e18r

From MySQL 5.5 Reference Manual section 13.1.17:

来自 MySQL 5.5 参考手册第13.1.17节：

If you do not have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY.

如果您没有 PRIMARY KEY 并且应用程序要求在您的表中提供 PRIMARY KEY，MySQL 将返回第一个没有 NULL 列的 UNIQUE 索引作为 PRIMARY KEY。

So, technically, the answer is no. However, as others have stated, in most cases it is quite useful.

因此，从技术上讲，答案是否定的。但是，正如其他人所说，在大多数情况下它非常有用。

回答by HLGEM

I firmly believe every table should have a way to uniquely identify a record. For 99% of the tables, this is a primary key. For the rest you may get away with a unique index (I'm thinking one column look up type tables here). Any time I have a had to work with a table without a way to uniquely identify records, there has been trouble.

我坚信每个表都应该有一种方法来唯一标识一条记录。对于 99% 的表，这是主键。对于其余部分，您可能会使用唯一索引（我认为此处有一列查找类型表）。每当我不得不使用一个表而无法唯一标识记录时，就会出现问题。

I also believe if you are using surrogate keys as your PK, you should, where at all possible, have a separate unique index on whatever combination of fields make up the natural key. I realize there are all too many times when you don't have a true natural key (names are not unique or what makes something unique might be spread across several parentchild tables), but if you do have one, please please please make sure it has a unique index or is created as the PK.

我还相信，如果您使用代理键作为您的 PK，您应该尽可能在构成自然键的任何字段组合上拥有单独的唯一索引。我意识到有太多次您没有真正的自然键（名称不是唯一的，或者使某些独特的东西可能分布在多个父子表中），但是如果您有一个，请确保它具有唯一索引或作为 PK 创建。

回答by lumpynose

If it's a join table then I wouldn't say that you need a primary key. Suppose, for example, that you have tables PERSONS, SICKPEOPLE, and ILLNESSES. The ILLNESSES table has things like flu, cold, etc., each with a primary key. PERSONS has the usual stuff about people, each also with a primary key. The SICKPEOPLE table only has people in it who are sick, and it has two columns, PERSONID and ILLNESSID, foreign keys back to their respective tables, and no primary key. The PERSONS and ILLNESSES tables contain entities and entities get primary keys. The entries in the SICKPEOPLE table aren't entities and don't get primary keys.

如果它是一个连接表，那么我不会说你需要一个主键。例如，假设您有表 PERSONS、SICKPEOPLE 和 ILLNESSES。ILLNESSES 表有流感、感冒等信息，每个都有一个主键。PERSONS 有关于人的常见内容，每个人也有一个主键。SICKPEOPLE 表中只有生病的人，它有两列，PERSONID 和 ILLNESSID，外键返回各自的表，没有主键。PERSONS 和 ILLNESSES 表包含实体，实体获取主键。SICKPEOPLE 表中的条目不是实体，也没有主键。

回答by Lluis Martinez

If there is no PK, how will you update or delete a single row ? It would be impossible ! To be honest I have used a few times tables without PK, for instance to store activity logs, but even in this case it is advisable to have one because the timestamps could not be granular enough. Temporary tables is another example. But according to relational theory the PK is mandatory.

如果没有PK，你将如何更新或删除单行？这是不可能的！老实说，我使用了几次没有 PK 的表，例如存储活动日志，但即使在这种情况下，也建议使用一个表，因为时间戳不够细。临时表是另一个例子。但根据关系理论，PK 是强制性的。

回答by user55474

it is good to have keys and relationships . Helps a lot. however if your app is good enough to handle the relationships then you could possibly skip the keys ( although i recommend that you have them )

有钥匙和关系是好的。有很大帮助。但是，如果您的应用程序足以处理这些关系，那么您可以跳过这些键（尽管我建议您拥有它们）

回答by Matt Dawdy

Since I use Subsonic, I always create a primary key for all of my tables. Many DB Abstraction libraries require a primary key to work.

由于我使用 Subsonic，我总是为我的所有表创建一个主键。许多数据库抽象库需要一个主键才能工作。

Note: that doesn't answer the "Grand Unified Theory" tone of your question, but I'm just saying that in practice, sometimes you MUST make a primary key for every table.

注意：这并不能回答您问题的“大统一理论”语气，但我只是说在实践中，有时您必须为每个表创建一个主键。