SQL 候选键和复合键有什么区别？

What is the difference between candidate key and composite key?

提问by Shailesh Jaiswal

I am reading about candidate keys and composite keys. I came to know that

我正在阅读有关候选键和复合键的信息。我开始知道

• a candidate key can qualify as a primary key and it can be a single column or combination of columns
• and a composite key is also a combination of columns.

• 候选键可以作为主键，它可以是单列或列组合
• 组合键也是列的组合。

For composite key I have referred this link

对于复合键，我已经提到了这个链接

how do I make a composite key with SQL Server Management Studio?

如何使用 SQL Server Management Studio 制作复合键？

Thus when both the candidate key and composite key are a combination of columns, they can qualify as a primary key. Then what is the exact difference ? Can you please explain with examples ?

因此，当候选键和复合键都是列的组合时，它们可以作为主键。那么具体的区别是什么呢？你能用例子解释一下吗？

采纳答案by Headshota

As I know candidate key is a unique key that can be used as a primary key. but not necessarily used as one.

据我所知，候选键是可以用作主键的唯一键。但不一定用作一个。

Composite key is a key of two or more attributes that uniquely identifies the row.

复合键是唯一标识行的两个或多个属性的键。

回答by Damien_The_Unbeliever

A key is a set of columns that can be used to uniquely identify each row within a table.

键是一组列，可用于唯一标识表中的每一行。

Every table has at least one key. Let's say we've identified each possible key for the table. Each of these keys is a candidate key.

每张桌子至少有一个键。假设我们已经确定了表的每个可能的键。这些键中的每一个都是候选键。

As we examine each of these keys, the key may consist of no columns (!), one column, or more than one column, which when considered together uniquely identify each row. The term composite key specifically refers to a key which consists of more than one column.

当我们检查这些键中的每一个时，键可能不包含任何列 (!)、一列或多于一列，当它们一起考虑时唯一标识每一行。术语组合键特指由多列组成的键。

In SQL, it was decided that one key should be selected and treated "more equal" than the other keys of the table. This key is called the primary key. Other keys can also be declared on the table, these are usually referred to as Unique Contsraints.

在 SQL 中，决定应该选择一个键并将其视为比表中的其他键“更平等”。这个键称为主键。也可以在表上声明其他键，这些通常称为唯一约束。

(!) In SQL, you aren't allowed to declare a key with no columns - although it would occasionally be useful (think of a table that should only ever have a single row, where each column is representing configuration information)

(!) 在 SQL 中，不允许声明没有列的键 - 尽管它偶尔会很有用（想想一个表应该只有一行，其中每一列代表配置信息）

---

---

As an example of a table which has multiple keys, all of which are composite. Imagine an appointment system, where a client and a counsellor meet in a room at a particular time:

作为具有多个键的表的示例，所有键都是复合的。想象一个预约系统，客户和顾问在特定时间在一个房间见面：

CREATE TABLE Appointments (
    ClientID int not null,
    CounsellorID int not null,
    RoomID int not null,
    AppointmentTime datetime not null
)

The candidate keys for this table are {ClientID,AppointmentTime}, {CounsellorID,AppointmentTime} and {RoomID,AppointmentTime}. Any of those combinations of columns could be used to uniquely identify a row in the table, and all of them are composite keys.

此表的候选键是 {ClientID,AppointmentTime}、{CounsellorID,AppointmentTime} 和 {RoomID,AppointmentTime}。任何这些列组合都可用于唯一标识表中的行，并且所有这些组合都是复合键。

Which one we choose to declare as primary key will depend (probably) on our own interpretation of the main "focus" of the system. Are we mainly concerned with Room usage, or Clients, or Counsellors? In any case, we'll select one and declare it the primary key. We'll also hopefully declare the other keys as unique constraints.

我们选择将哪一个声明为主键将取决于（可能）我们自己对系统主要“焦点”的解释。我们主要关心房间的使用，还是客户，或顾问？在任何情况下，我们都会选择一个并将其声明为主键。我们也希望将其他键声明为唯一约束。

Or, we could decide to use a surrogate, and declare an AppointmentIDcolumn, using whatever auto-numbering facilities are available in the database. That could then be the (non-composite) primary key. But we should still declare the other keys for the table.

或者，我们可以决定使用代理并声明一个AppointmentID列，使用数据库中可用的任何自动编号功能。那可能是（非复合）主键。但是我们仍然应该为表声明其他键。

回答by Ali Ben Messaoud

Candidate Key:A nominee for primary key field is known as candidate key.

候选键：主键字段的提名者称为候选键。

Composite Key:Creating more than one primary key is jointly known as composite key.

复合键：创建多个主键统称为复合键。

Update :A candidate key is a unique key that can be used as a primary key. Composite key is a key of two or more attributes that uniquely identifies the row. A key is a set of columns that can be used to uniquely identify each row within a table.

更新：候选键是可以用作主键的唯一键。复合键是唯一标识行的两个或多个属性的键。键是一组列，可用于唯一标识表中的每一行。

回答by KamalDeep

CANDIDATE KEY:- Candidate key is a unique key and is a "Candidate" for being a primary key. COMPOSITE KEY:- "Composition" of two or more columns as primary key, is consider as Composite key.

CANDIDATE KEY:- 候选键是一个唯一键，是作为主键的“候选键”。 复合键：- 两列或多列的“组合”作为主键，被视为复合键。

回答by jmoreno

A candidate key is just that: a column or combination of columns that couldbe used as the primary key, ie a candidate or potential primary key, A composite key is by definition two or more columns that could be used to identify a row. Most commonly when talking about composite keys the question is whether to have it as the primary key to have a surrogate key instead. On occassion you may hear people refer to a composite key that is not a primary key (ie not unique for all rows), in which case it is (hopefully) just a way to refer to a multi-colmn join on non-primary key fields (instead of doing cross join and filtering the rows in the where clause).

候选关键就是：该列的列或组合可以被用来作为主键，即候选或潜在的主键，A复合键是通过定义的两个或可用于识别行多个列。在谈论复合键时，最常见的问题是是否将其作为主键来代替代理键。有时，您可能会听到人们提到的复合键不是主键（即并非所有行都是唯一的），在这种情况下，它（希望）只是引用非主键上的多列连接的一种方式字段（而不是在 where 子句中进行交叉连接和过滤行）。