SQL 超键、候选键和主键

Superkey, candidate key & primary key

提问by JLearner

Can any kind soul clarify my doubts with a simple example below and identify the superkey, candidate key and primary key?

哪位好心人可以用下面的简单例子澄清我的疑惑，并确定超键、候选键和主键吗？

I know there are a lot of posts and websites out there explaining the differences between them. But it looks like all are generic definitions.

我知道有很多帖子和网站解释了它们之间的差异。但看起来都是通用定义。

Example:

例子：

Student (StudentNumber, FamilyName, Degree, Major, Grade, PhoneNumber)

So from the above example, I can know StudentNumberis a primary key.

所以从上面的例子，我可以知道StudentNumber是一个主键。

But as for superkey, I'm a bit confused what combination of attributes could be grouped into the superkey?

但是对于超级键，我有点困惑可以将哪些属性组合分组到超级键中？

As for candidate key, I'm confused by the definition given as any candidate key can qualify as a primary key.

至于候选键，我对给出的定义感到困惑，因为任何候选键都可以作为主键。

Does it mean that attributes such as PhoneNumberare a candidate key and can be a primary key? (Assuming that a PhoneNumberonly belongs to one student)

这是否意味着诸如PhoneNumber候选键之类的属性可以是主键？（假设aPhoneNumber只属于一个学生）

Thanks for any clarification!

感谢您的澄清！

回答by Cambium

Since you don't want textbook definitions, loosely speaking, a super key is a set of columns that uniquely defines a row.

由于您不想要教科书定义，因此松散地说，超级键是一组唯一定义行的列。

This set can have one or more elements, and there can be more than one super key for a table. You usually do this through functional dependencies.

这个集合可以有一个或多个元素，一张表可以有多个超级键。你通常通过函数依赖来做到这一点。

In your example, I'm assuming:

在你的例子中，我假设：

StudentNumber    unique
FamilyName     not unique
Degree     not unique
Major      not unique
Grade      not unique
PhoneNumber    not unique

In this case, a superkey is any combination that contains the student number.

在这种情况下，超键是包含学号的任意组合。

So the following are superkeys

所以以下是超级键

StudentNumber
StudentNumber, FamilyName
StudentNumber, FamilyName, Degree
StudentNumber, FamilyName, Degree, Major
StudentNumber, FamilyName, Degree, Major, Grade
StudentNumber, FamilyName, Degree, Major, Grade, PhoneNumber
StudentNumber, Degree
StudentNumber, Degree, Major
StudentNumber, Degree, Major, Grade
StudentNumber, Degree, Major, Grade, PhoneNumber
StudentNumber, Major
StudentNumber, Major, Grade
StudentNumber, Major, Grade, PhoneNumber
StudentNumber, Grade
StudentNumber, Grade, PhoneNumber
StudentNumber, PhoneNumber

Now assume, if PhoneNumber is unique (who shares phones these days), then the following are also superkeys (in addition to what I've listed above).

现在假设，如果 PhoneNumber 是唯一的（这些天谁共享电话），那么以下也是超级键（除了我上面列出的）。

PhoneNumber
PhoneNumber, Grade, 
PhoneNumber, Major, Grade
PhoneNumber, Degree, Major, Grade
PhoneNumber, FamilyName, Degree, Major, Grade
PhoneNumber, Major
PhoneNumber, Degree, Major
PhoneNumber, FamilyName, Degree, Major
PhoneNumber, StudentNumber, FamilyName, Degree, Major
PhoneNumber, Degree
PhoneNumber, FamilyName, Degree
PhoneNumber, StudentNumber, FamilyName, Degree
PhoneNumber, FamilyName
PhoneNumber, StudentNumber, FamilyName

A candidate key is simply the "shortest" superkey. Going back to the 1st list of superkeys (i.e. phone number isn't unique), the shortest superkey is StudentNumber.

候选键只是“最短”的超级键。回到超级键的第一个列表（即电话号码不是唯一的），最短的超级键是 StudentNumber。

The primary key is usually just the candidate key.

主键通常只是候选键。

回答by nvogel

A superkey is any set of attributes for which the values are guaranteed to be unique for all possible sets of tuples in a table at all times.

超级键是任何一组属性，其值对于表中所有可能的元组集始终是唯一的。

A candidate key is a "minimal" superkey - meaning the smallest subset of superkey attributes which are unique. Removing any attribute from a candidate key would therefore make it non-unique.

候选键是“最小”超键 - 意味着唯一的超键属性的最小子集。因此，从候选键中删除任何属性都会使其不唯一。

A primary key is just a candidate key. There is no difference between a primary key and any other candidate key.

主键只是一个候选键。主键和任何其他候选键之间没有区别。

It's not really useful to make assumptions about keys based only on a list of attribute names. You need to know what dependencies are supposed to hold between the attributes. Having said that, my guess is that you are right - StudentNumber is likely a candidate key in your example.

仅根据属性名称列表对键进行假设并不是很有用。您需要知道属性之间应该保持哪些依赖关系。话虽如此，我的猜测是您是对的 - StudentNumber 可能是您示例中的候选键。

回答by JerryGoyal

Stretching Cambium's answer, if the PhoneNumberis also unique along with StudentNumberthen candidate keyswould be:- {StudentNumber},{PhoneNumber}.
Here we can't assume {StudentNumber,PhoneNumber}as a single candidate keybecause if we omit one attribute say StudentNumberwe still get a unique attribute{PhoneNumber}thus, violating the definition of candidate key.

Primary key:Choose one candidate keyout of all candidate keys. There are 2 candidate keysso we can choose {StudentNumber}as primary key.
Alternate keys:leftover candidate keys, after choosing primary keyfrom candidate keys, are alternate keys i.e. {PhoneNumber}.

拉伸 Cambium 的答案，如果PhoneNumber也是唯一的，StudentNumber那么candidate keys将是：- {StudentNumber}, {PhoneNumber}。
在这里我们不能假设{StudentNumber,PhoneNumber}为单一的，candidate key因为如果我们省略一个属性，StudentNumber我们仍然会得到一个唯一的属性，{PhoneNumber}因此违反了 的定义candidate key。从所有中

Primary key:选择一个。有 2 个所以我们可以选择as 。剩下的，选择后，是备用键，即。candidate keycandidate keyscandidate keys{StudentNumber}primary key
Alternate keys:candidate keysprimary keycandidate keys{PhoneNumber}

compound key:a compound key is a key that consists of two or more attributes that uniquely identify an entity occurrence. A simple key is one that has only one attribute. Compound keys may be composed of other unique simple keys and non-key attributes, but may not include another compound key.

compound key:复合键是由两个或多个唯一标识实体出现的属性组成的键。简单键是只有一个属性的键。复合键可以由其他唯一的简单键和非键属性组成，但不能包含另一个复合键。

composite key:A composite key contains at least one compound key and one more attribute. Composite keys may also include simple keys and non-key attributes.

composite key:一个复合键至少包含一个复合键和一个属性。复合键还可以包括简单键和非键属性。