database 每个表都应该有一个主键吗？

Should each and every table have a primary key?

提问by Daniel Silveira

I'm creating a database table and I don't have a logical primary key assigned to it. So, I'm thinking about leaving it without a primary key, but I'm feeling a bit guilty about it. Should I?

我正在创建一个数据库表，但我没有分配给它的逻辑主键。所以，我想在没有主键的情况下离开它，但我对此感到有点内疚。我是不是该？

Should each and every table have a primary key?

每个表都应该有一个主键吗？

采纳答案by Quassnoi

Short answer: yes.

简短的回答：是的。

Long answer:

长答案：

• You need your table to be joinable on something
• If you want your table to be clustered, you need some kind of a primary key.
• If your table design does not need a primary key, rethink your design: most probably, you are missing something. Why keep identical records?

• 你需要你的桌子可以加入一些东西
• 如果您希望对表进行集群，则需要某种主键。
• 如果您的表设计不需要主键，请重新考虑您的设计：很可能您遗漏了一些东西。为什么要保留相同的记录？

In MySQL, the InnoDB storage engine always creates a primary key if you didn't specify it explicitly, thus making an extra column you don't have access to.

在 MySQL 中，如果您没有明确指定，InnoDB 存储引擎总是会创建一个主键，从而产生一个您无权访问的额外列。

Note that a primary key can be composite.

请注意，主键可以是复合的。

If you have a many-to-many link table, you create the primary key on all fields involved in the link. Thus you ensure that you don't have two or more records describing one link.

如果您有一个多对多链接表，您可以在链接中涉及的所有字段上创建主键。因此，您可以确保没有两个或多个记录描述一个链接。

Besides the logical consistency issues, most RDBMS engines will benefit from including these fields in a unique index.

除了逻辑一致性问题，大多数 RDBMS 引擎将受益于将这些字段包含在唯一索引中。

And since any primary key involves creating a unique index, you should declare it and get both logical consistency and performance.

由于任何主键都涉及创建唯一索引，因此您应该声明它并获得逻辑一致性和性能。

See this article in my blog for why you should always create a unique index on unique data:

请参阅我博客中的这篇文章，了解为什么应该始终为唯一数据创建唯一索引：

• Making an index UNIQUE

• 使索引唯一

P.S.There are some very, veryspecial cases where you don't need a primary key.

PS有一些非常非常特殊的情况，您不需要主键。

Mostly they include log tables which don't have anyindexes for performance reasons.

大多数情况下，它们包括出于性能原因没有任何索引的日志表。

回答by Michael Wheeler

Always best to have a primary key. This way it meets first normal formand allows you to continue along the database normalizationpath.

总是最好有一个主键。这样它满足第一范式，并允许您沿着数据库规范化路径继续。

As stated by others, there are some reasons not to have a primary key, but most will not be harmed if there is a primary key

正如其他人所说，没有主键有一些原因，但如果有主键，大多数不会受到伤害

回答by marc_s

Except for a few very rare cases (possibly a many-to-many relationship table, or a table you temporarily use for bulk-loading huge amounts of data), I would go with the saying:

除了一些非常罕见的情况（可能是多对多关系表，或者临时用于批量加载大量数据的表），我会同意这样的说法：

If it doesn't have a primary key, it's not a table!

如果它没有主键，就不是表！

Marc

马克

回答by tvanfosson

Pretty much any time I've created a table without a primary key, thinking I wouldn't need one, I've ended up going back and adding one. I now create even my join tables with an auto-generated identity field that I use as the primary key.

几乎任何时候我创建了一个没有主键的表，认为我不需要一个，我最终会回去添加一个。我现在甚至使用我用作主键的自动生成的标识字段创建我的连接表。

回答by HLGEM

Will you ever need to join this table to other tables? Do you need a way to uniquely identify a record? If the answer is yes, you need a primary key. Assume your data is something like a customer table that has the names of the people who are customers. There may be no natural key because you need the addresses, emails, phone numbers, etc. to determine if this Sally Smith is different from that Sally Smith and you will be storing that information in related tables as the person can have mulitple phones, addesses, emails, etc. Suppose Sally Smith marries John Jones and becomes Sally Jones. If you don't have an artifical key onthe table, when you update the name, you just changed 7 Sally Smiths to Sally Jones even though only one of them got married and changed her name. And of course in this case withouth an artificial key how do you know which Sally Smith lives in Chicago and which one lives in LA?

您是否需要将此表连接到其他表？您是否需要一种方法来唯一标识记录？如果答案是肯定的，则您需要一个主键。假设您的数据类似于包含客户姓名的客户表。可能没有自然键，因为您需要地址、电子邮件、电话号码等来确定这个 Sally Smith 是否与那个 Sally Smith 不同，并且您将将该信息存储在相关表中，因为此人可以有多个电话、地址、电子邮件等。假设 Sally Smith 与 John Jones 结婚并成为 Sally Jones。如果桌面上没有人造钥匙，当您更新名称时，您只是将 7 Sally Smiths 更改为 Sally Jones，即使其中只有一个结婚并更改了她的名字。

You say you have no natural key, therefore you don't have any combinations of field to make unique either, this makes the artficial key critical.

您说您没有自然键，因此您也没有任何字段组合来使其唯一，这使得人工键变得至关重要。

I have found anytime I don't have a natural key, an artifical key is an absolute must for maintaining data integrity. If you do have a natural key, you can use that as the key field instead. But personally unless the natural key is one field, I still prefer an artifical key and unique index on the natural key. You will regret it later if you don't put one in.

我发现任何时候我都没有自然键，人工键是维护数据完整性的绝对必要条件。如果您确实有一个自然键，则可以将其用作键字段。但就个人而言，除非自然键是一个字段，否则我还是更喜欢自然键上的人工键和唯一索引。如果你不放一个，你以后会后悔的。

回答by rapha?λ

Just add it, you will be sorry later when you didn't (selecting, deleting. linking, etc)

只需添加它，如果你没有（选择，删除。链接等），你会后悔的

回答by endo64

It is a good practice to have a PK on every table, but it's not a MUST. Most probably you will need a unique index, and/or a clustered index (which is PK or not) depending on your need.

在每张桌子上都有一个 PK 是一个好习惯，但这不是必须的。根据您的需要，您很可能需要一个唯一索引和/或聚集索引（是否为 PK）。

Check out the Primary Keys and Clustered Indexes sections on Books Online (for SQL Server)

查看联机丛书中的主键和聚集索引部分（适用于 SQL Server）

"PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table. No two rows in a table can have the same primary key value. You cannot enter NULL for any column in a primary key. We recommend using a small, integer column as a primary key. Each table should have a primary key. A column or combination of columns that qualify as a primary key value is referred to as a candidate key."

" PRIMARY KEY 约束标识具有唯一标识表中行的值的列或列集。表中没有两行可以具有相同的主键值。您不能为主键中的任何列输入 NULL。我们建议使用小的整数列作为主键。每个表都应该有一个主键。符合主键值的列或列组合称为候选键。”

But then check this out also: http://www.aisintl.com/case/primary_and_foreign_key.html

但也请检查一下：http: //www.aisintl.com/case/primary_and_foreign_key.html

回答by Theodore Zographos

Disagree with the suggested answer. The short answer is: NO.

不同意建议的答案。简短的回答是：不。

The purpose of the primary key is to uniquely identify a row on the table in order to form a relationship with another table. Traditionally, an auto-incremented integer value is used for this purpose, but there are variations to this.

主键的目的是唯一标识表上的一行，以便与另一个表形成关系。传统上，自动递增的整数值用于此目的，但也有变化。

There are cases though, for example logging time-series data, where the existence of a such key is simply not needed and just takes up memory. Making a row unique is simply ...not required!

但是在某些情况下，例如记录时间序列数据，根本不需要这样的键的存在，只需要占用内存。使行唯一只是...不是必需的！

A small example: Table A: LogData

一个小例子：表A：LogData

Columns:  DateAndTime, UserId, AttribA, AttribB, AttribC etc...

No Primary Key needed.

不需要主键。

Table B: User

表 B：用户

Columns: Id, FirstName, LastName etc. 

Primary Key (Id) needed in order to be used as a "foreign key" to LogData table.

需要主键 (Id) 才能用作 LogData 表的“外键”。

回答by StewNoble

To make it future proof you really should. If you want to replicate it you'll need one. If you want to join it to another table your life (and that of the poor fools who have to maintain it next year) will be so much easier.

为了让它成为未来的证明，你真的应该这样做。如果你想复制它，你需要一个。如果你想把它加入另一张桌子，你的生活（以及明年必须维护它的可怜的傻瓜的生活）会容易得多。

回答by Tacoman667

I know that in order to use certain features of the gridview in .NET, you need a primary key in order for the gridview to know which row needs updating/deleting. General practice should be to have a primary key or primary key cluster. I personally prefer the former.

我知道为了在 .NET 中使用 gridview 的某些功能，您需要一个主键，以便 gridview 知道哪一行需要更新/删除。一般的做法应该是有一个主键或主键集群。我个人更喜欢前者。