复合主键与附加“ID"柱子?

2021-12-05 00:00:00 sql database sql-server database-design

如果我们有一张这样的桌子:

If we had a table like this:

书籍(假装ISBN"不存在)

Books (pretend "ISBN" doesn't exist)

  • 作者
  • 标题
  • 版本
  • 出版年份
  • 价格

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

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

是什么决定候选/主键应该是 {Author,Title,Edition} 还是应该使用 ID 列，{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?

也是

  • 作者(PK)
  • 标题(PK)
  • 版本(PK)
  • 出版年份
  • 价格

更好，或者:

  • 身份证(PK)
  • 作者
  • 标题
  • 版本
  • 出版年份
  • 价格

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

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

推荐答案

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

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

  1. 它是一个超级键——唯一标识一个元组(行).

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

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

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

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

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

现在让我们考虑 ID(整数)

Now let's consider the ID (integer)

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

I can reason that the Book table 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 表中添加一个唯一整数列，用作键，将作为外键引用.像这样说:

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

alter table Book add BookID integer not null identity;

由于 BookID 也是(必须)唯一的，Book 表现在有两个候选键.

With BookID being (must be) unique too, the Book table now has two candidate keys.

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

Now I can select the BookID as a primary key.

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

但是，{Author,Title,Edition} 必须保持一个键(唯一)以防止这样的事情:

However, the {Author,Title,Edition} must stay a key (unique) in order to prevent something like this:

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

总而言之，添加 BookID -- 并将其选为主要 -- 并没有阻止 {Author, Title, Edition} 成为(候选)钥匙.它仍然必须有自己的唯一约束，通常还有匹配的索引.

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.

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

Also note that from the design point, this decision was done on the "physical level". In general, on the logical level of design, this ID does 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.

相关文章