SQL 复合主键与附加“ID”列？

Composite Primary key vs additional "ID" column?

提问by user997112

If we had a table like this:

如果我们有一张这样的表：

Books (pretend "ISBN" doesn't exist)

书籍（假装“ISBN”不存在）

• Author
• Title
• Edition
• Year of publication
• Price

• 作者
• 标题
• 版
• 出版年份
• 价钱

One could argue that {Author,Title,Edition} could be a candidate/primary key.

有人可能会争辩说 {Author,Title,Edition} 可能是候选键/主键。

What determines whether the candidate/primary key should be {Author,Title,Edition} or whether an ID column should be used, with {Author,Title,Edition} a unique index/key constraint?

什么决定候选键/主键应该是 {Author,Title,Edition} 还是应该使用 ID 列，{Author,Title,Edition} 是唯一的索引/键约束？

So is

也是

• Author (PK)
• Title (PK)
• Edition (PK)
• Year of publication
• Price

• 作者（PK）
• 标题（PK）
• 版（PK）
• 出版年份
• 价钱

better, or:

更好，或者：

• ID (PK)
• Author
• Title
• Edition
• Year of publication
• Price

• 身（PK）
• 作者
• 标题
• 版
• 出版年份
• 价钱

where {Author,Title,Edition} is an additional unique index/constraint?

其中 {Author,Title,Edition} 是一个额外的唯一索引/约束？

采纳答案by Damir Sudarevic

Say that {Author, Title, Edition}uniquely identifies a book, then the following holds:

说{Author, Title, Edition}唯一标识一本书，那么以下成立：

1. It is a superkey -- uniquely identifies a tuple (row).

2. It is irreducible -- removing any of the columns does not make it a key any more.

3. It is a candidate key -- an irreducible superkey is a candidate key.

1. 它是一个超级键——唯一标识一个元组（行）。

2. 它是不可约的——删除任何列都不再使它成为键。

3. 它是一个候选键——一个不可约的超键是一个候选键。

Now let's consider the ID (integer)

现在让我们考虑 ID（整数）

I can reason that the Booktable key will show up in few other tables as a foreign key and also in few indexes. So, it will take quite a bit of space -- say three columns x 40 characters (or whatever...) -- in each of these tables plus in matching indexes.

我可以推断Book表键会作为外键出现在其他几个表中，也很少出现在索引中。因此，在这些表中的每一个加上匹配的索引中，它将占用相当多的空间——比如三列 x 40 个字符（或其他任何……）。

In order to make these "other" tables and indexes smaller, I can add a unique-integer-column to the Booktable to be used as a key which will be referenced as a foreign key. Say something like:

为了使这些“其他”表和索引更小，我可以向表中添加一个唯一整数列以Book用作将作为外键引用的键。像这样说：

alter table Book add BookID integer not null identity;

With BookIDbeing (must be) unique too, the Booktable now has two candidate keys.

由于BookID（必须）也是唯一的，该表现在Book有两个候选键。

Now I can select the BookIDas a primary key.

现在我可以选择BookID作为主键。

alter table Book add constraint pk_Book primary key (BookID);

However, the {Author,Title,Edition}muststay a key (unique) in order to preventsomething like this:

但是，{Author,Title,Edition}必须保留一个键（唯一的）以防止这样的事情：

BookID  Author      Title           Edition
-----------------------------------------------
  1      C.J.Date  Database Design     1
  2      C.J.Date  Database Design     1

To sum it up, adding the BookID-- and choosing it as the primary -- did not stop {Author, Title, Edition}being a (candidate) key. It still must have its own unique constraint and usually the matching index.

总而言之，添加BookID-- 并选择它作为主要 -- 并没有停止 {Author, Title, Edition}成为（候选）键。它仍然必须有自己的唯一约束，通常还有匹配的索引。

Also note that from the design point, this decision was done on the "physical level". In general, on the logical level of design, this IDdoes not exists -- it got introduced during the consideration of column sizes and indexes. So, the physical schema was derived from the logical one. Depending on the DB size, RDBMS and hardware used, none of that size-reasoning may have measurable effect -- so using {Author, Title, Edition}as a PK may be perfectly good design -- until proven differently.

另请注意，从设计角度来看，此决定是在“物理级别”上完成的。通常，在设计的逻辑级别上，这ID并不存在——它是在考虑列大小和索引时引入的。因此，物理模式源自逻辑模式。根据所使用的 DB 大小、RDBMS 和硬件，这些大小推理都不会产生可衡量的效果——因此{Author, Title, Edition}用作 PK 可能是非常好的设计——除非得到不同的证明。

回答by Gilbert Le Blanc

In general, you don't want the primary key to change value. This is why blind or surrogate primary keys are used.

通常，您不希望主键更改值。这就是使用盲主键或代理主键的原因。

Let's assume you created your Book table with Author as part of the primary key.

假设您使用 Author 作为主键的一部分创建了 Book 表。

Suppose you found out after about a year that you misspelled "Ray Bradbury". Or even worse, you misspelled "Rachael Bloom". Just imagine how many database rows you would have to modify to correct the misspelling. Just imagine how many index references have to be changed.

假设您在大约一年后发现您拼错了“Ray Bradbury”。或者更糟的是，你拼错了“Rachael Bloom”。想象一下，您必须修改多少数据库行才能纠正拼写错误。想象一下有多少索引引用必须被改变。

However, if you have an Author table with a surrogate key, you only have to correct one row. No indexes have to be changed.

但是，如果您有一个带有代理键的 Author 表，您只需更正一行。无需更改任何索引。

Finally, database table names are usually singular (Book), rather than plural (Books).

最后，数据库表名通常是单数（Book），而不是复数（Books）。

回答by Scotty Boy

Another good reason for using the surrogate primary key scenario is if the uniqueness constraint should change in the future (say, ISBN needs to be added to make a book unique). Rekeying your data will be much easier.

使用代理主键场景的另一个很好的理由是唯一性约束是否应该在未来发生变化（例如，需要添加 ISBN 以使书籍唯一）。重新加密您的数据会容易得多。

回答by Petrutiu Mihai

There are many articles related to this. The problems with composite key in your case:

有很多相关的文章。您的情况下复合键的问题：

1. hard to link books with other entities
2. Hard to edit them in a grid as most grids are not supporting composite keys (e.g. kendo grid, jqgrid)
3. You might misspell Author, Title, Edition

1. 很难将书籍与其他实体联系起来
2. 很难在网格中编辑它们，因为大多数网格不支持复合键（例如剑道网格、jqgrid）
3. 您可能会拼错作者、标题、版本

It would be also good to normalize your data and store just an ID to the author like (dasblinkenlight) suggested. Worst case scenario, he/she will change his/hers name (e.g. she get's married, and she likes her new name).

将您的数据标准化并仅将 ID 存储给作者（如 (dasblinkenlight) 建议的那样）也很好。最坏的情况，他/她会改变他/她的名字（例如她结婚了，她喜欢她的新名字）。