复合主键与附加“ID”柱？ [英] Composite Primary key vs additional "ID" column?

问题描述

如果我们有这样的表格：

图书（假装ISBN不存在）

• 作者


• 标题


• 版本


• li>
  
• 价格

可以说{author，Title，Edition}键。

决定候选人/主键是{Author，Title，Edition}还是应使用ID列，{author，Title，Edition}

• 作者（PK）
  / li>
  
• 标题（PK）


• 版本（PK）


• 出版年份


• 价格

更好，或：

• ID（PK）


• 作者


• 标题


• 版本


• 出版年度


• 价格

{作者，标题，版本} / code>唯一标识一本书，则以下条件成立：

2. 这是不可约的 - 删除任何列不会再成为一个键。

   / li>
   

3. 这是候选键 - 不可约键是候选键。

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

我可以推论 Book 显示在少数其他表作为外键和几个索引。所以，它将需要相当多的空间 - 说三个列x 40个字符（或任何...） - 在每个表中加上匹配索引。

为了使这些其他表和索引更小，我可以添加一个unique-integer-column到 Book 表用作将被引用为外键的键。说如下：

  alter table`Book`添加`BookID` integer not null identity; 
  

使用 BookID Book 表现在有两个候选键。

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

  alter table书添加约束pk_Book主键（BookID）; 
  

但是， {Author，Title，Edition} 必须保留一个键（唯一），以防止：

  BookID作者标题版本
 ------------------------------------- ---------- 
 1 CJDate数据库设计1 
 2 CJDate数据库设计1 
  

总结，添加 BookID - 并选择它作为主要 - 没有停止 {作者，标题，版本} 作为（候选）键。它仍然必须有自己的唯一约束和通常匹配的索引。

还要注意，从设计的角度来看，这个决定是在物理级别。
一般来说，在逻辑设计层面，这个 ID 不存在 - 它是在考虑列大小和索引时引入的。所以物理模式是从逻辑模式派生的。根据所使用的数据库大小，RDBMS和硬件，这些大小推理都没有可测量的效果 - 所以使用 {Author，Title，Edition} 完美的设计 - 直到证明不同。

If we had a table like this:

Books (pretend "ISBN" doesn't exist)

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

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

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?

So is

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

better, or:

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

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

解决方案

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

1. It is a (super)key -- 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 key is a candidate key.

Now let's consider the ID (integer)

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.

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;

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

Now I can select the BookID as a primary key.

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

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

Tu 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.

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.

这篇关于复合主键与附加“ID”柱？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！