SQL 主键和唯一约束有什么区别？

What is the difference between a primary key and a unique constraint?

提问by Tim

Someone asked me this question on an interview...

面试时有人问我这个问题...

采纳答案by Andrew Hare

A primary key isa unique field on a table but it is special in the sense that the table considers that row as its key. This means that other tables can use this field to create foreign key relationships to themselves.

主键是表上的一个唯一字段，但它的特殊之处在于表将该行视为其键。这意味着其他表可以使用此字段来创建与其自身的外键关系。

A unique constraint simply means that a particular field must be unique.

唯一约束仅意味着特定字段必须是唯一的。

回答by H?vard

Primary keys can't be null. Unique keys can.

主键不能为空。唯一键可以。

回答by Giriraj Govil

1. Primary key can not be null but unique can have only one null value.
2. Primary key create the cluster index automatically but unique key not.
3. A table can have only one primary key but unique key more than one.

1. 主键不能为空，但唯一的只能有一个空值。
2. 主键会自动创建集群索引，但唯一键不会。
3. 一张表只能有一个主键，但唯一键不止一个。

回答by onedaywhen

TL;DR Much can be implied by PRIMARY KEY(uniqueness, reference-able, non-null-ness, clustering, etc) but nothing that can't be stated explicitly using UNIQUE.

TL;DR 很多可以通过PRIMARY KEY（唯一性、可引用、非空性、聚类等）暗示，但没有什么不能使用UNIQUE.

I suggest that if you are the kind of coder who likes the convenience of SELECT * FROM...without having to list out all those pesky columns then PRIMARY KEYis just the thing for you.

我建议，如果你是那种喜欢方便SELECT * FROM...而不必列出所有那些讨厌的列的编码员，那么PRIMARY KEY这就是你的选择。

---

---

a relvar can have several keys, but we choose just one for underlining and call that one the primary key. The choice is arbitrary, so the concept of primary is not really very important from a logical point of view. The general concept of key, however, is very important! The term candidate key means exactly the same as key (i.e., the addition of candidate has no real significance—it was proposed by Ted Codd because he regarded each key as a candidate for being nominated as the primary key)... SQL allows a subset of a table's columns to be declared as a key for that table. It also allows one of them to be nominated as the primary key. Specifying a key to be primary makes for a certain amount of convenience in connection with other constraints that might be needed

一个 relvar 可以有多个键，但我们只选择一个作为下划线并将其称为主键。选择是任意的，因此从逻辑的角度来看，主要的概念并不是很重要。然而，键的一般概念非常重要！术语候选键的含义与键完全相同（即，候选键的添加没有实际意义——它是由 Ted Codd 提出的，因为他将每个键视为被提名为主键的候选键）...... SQL 允许要声明为该表的键的表列的子集。它还允许将其中之一指定为主键。将一个键指定为主键可以为可能需要的其他约束带来一定的便利

What Is a Key? by Hugh Darwen

什么是钥匙？通过休·达文

---

---

it's usual... to single out one key as the primary key (and any other keys for the relvar in question are then said to be alternate keys). But whether some key is to be chosen as primary, and if so which one, are essentially psychological issues, beyond the purview of the relational model as such. As a matter of good practice, most base relvars probably should have a primary key—but, to repeat, this rule, if it is a rule, really isn't a relational issue as such... Strong recommendation [to SQL users]: For base tables, at any rate, use PRIMARY KEY and/or UNIQUE specifications to ensure that every such table does have at least one key.

通常……将一个键单独作为主键（然后将相关 relvar 的任何其他键称为备用键）。但是，是否要选择某个键作为主要键，如果是，是哪个键，本质上是心理问题，超出了关系模型本身的范围。作为一个好的实践，大多数基本相关变量可能应该有一个主键——但是，重复一遍，这个规则，如果它是一个规则，那么真的不是一个关系问题......强烈推荐[给 SQL 用户] ：对于基表，无论如何，使用 PRIMARY KEY 和/或 UNIQUE 规范来确保每个这样的表至少有一个键。

SQL and Relational Theory: How to Write Accurate SQL CodeBy C. J. Date

SQL 和关系理论：如何按 CJ 日期编写准确的 SQL 代码

In standard SQL PRIMARY KEY

在标准 SQL 中 PRIMARY KEY

• implies uniqueness but you can specify that explicitly (using UNIQUE).
• implies NOT NULLbut you can specify that explicitly when creating columns (but you should be avoiding nulls anyhow!)
• allows you to omit its columns in a FOREIGN KEYbut you can specify them explicitly.
• can be declared for only one key per table but it is not clear why (Codd, who originally proposed the concept, did not impose such a restriction).

• 暗示唯一性，但您可以明确指定（使用UNIQUE）。
• 暗示NOT NULL但您可以在创建列时明确指定（但无论如何您应该避免空值！）
• 允许您在 a 中省略其列，FOREIGN KEY但您可以明确指定它们。
• 可以为每个表只声明一个键，但不清楚为什么（最初提出这个概念的 Codd 没有强加这样的限制）。

In some products PRIMARY KEYimplies the table's clustered index but you can specify that explicitly (you may not want the primary key to be the clustered index!)

在某些产品中PRIMARY KEY暗示表的聚集索引，但您可以明确指定（您可能不希望主键是聚集索引！）

For some people PRIMARY KEYhas purely psychological significance:

对某些人来说 PRIMARY KEY具有纯粹的心理意义：

• they think it signifies that the key will be referenced in a foreign key (this was proposed by Codd but not actually adopted by standard SQL nor SQL vendors).
• they think it signifies the sole key of the table (but the failure to enforce other candidate keys leads to loss of data integrity).
• they think it implies a 'surrogate' or 'artificial ' key with no significance to the business (but actually imposes unwanted significance on the enterprise by being exposed to users).

• 他们认为这意味着将在外键中引用该键（这是 Codd 提出的，但实际上并未被标准 SQL 或 SQL 供应商采用）。
• 他们认为它表示表的唯一键（但未能强制执行其他候选键会导致数据完整性丢失）。
• 他们认为这意味着一个“代理”或“人工”密钥，对业务没有意义（但实际上通过暴露给用户而对企业强加了不必要的意义）。

回答by prnawa

Unique Key constraints:

唯一键约束：

1. Unique key constraint will provide you a constraint like the column values should retain uniqueness.
2. It will create non-clustered index by default
3. Any number of unique constraints can be added to a table.

4. It will allow null value in the column.

   ALTER TABLE table_name ADD CONSTRAINT UNIQUE_CONSTRAINT UNIQUE (column_name1, column_name2, ...)

1. 唯一键约束将为您提供一个约束，例如列值应保持唯一性。
2. 默认情况下会创建非聚集索引
3. 可以将任意数量的唯一约束添加到表中。

4. 它将允许列中的空值。

   ALTER TABLE table_name ADD CONSTRAINT UNIQUE_CONSTRAINT UNIQUE (column_name1, column_name2, ...)

Primary Key:

首要的关键：

1. Primary key will create column data uniqueness in the table.
2. Primary key will create clustered index by default
3. Only one Primay key can be created for a table
4. Multiple columns can be consolidated to form a single primary key

5. It wont allow null values.

   ALTER TABLE table_name ADD CONSTRAINT KEY_CONSTRAINT PRIMARY KEY (column_name)

1. 主键将在表中创建列数据的唯一性。
2. 主键默认会创建聚集索引
3. 只能为一张表创建一个 Primay 键
4. 可以合并多个列以形成单个主键

5. 它不允许空值。

   ALTER TABLE table_name ADD CONSTRAINT KEY_CONSTRAINT PRIMARY KEY (column_name)

回答by Beth

Every primary key is a unique constraint, but in addition to the PK, a table can have additional unique constraints.

每个主键都是唯一约束，但除了 PK 之外，表还可以有额外的唯一约束。

Say you have a table Employees, PK EmployeeID. You can add a unique constraint on SSN, for example.

假设您有一个表Employees，PK EmployeeID。例如，您可以在 SSN 上添加唯一约束。

回答by Kevin LaBranche

In addition to Andrew's answer, you can only have one primary key per table but you can have many unique constraints.

除了 Andrew 的回答之外，每个表只能有一个主键，但可以有许多唯一约束。

回答by C?t?lin Piti?

1. Primary key's purpose is to uniquely identify a row in a table. Unique constraint ensures that a field's value is unique among the rows in table.
2. You can have only one primary key per table. You can have more than one unique constraint per table.

1. 主键的目的是唯一标识表中的一行。唯一约束确保字段的值在表中的行中是唯一的。
2. 每个表只能有一个主键。每个表可以有多个唯一约束。

回答by Manjeet Barnala

The UNIQUE constraint uniquely identifies each record in a database table.

UNIQUE 约束唯一标识数据库表中的每条记录。

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

UNIQUE 和 PRIMARY KEY 约束都为一列或一组列的唯一性提供了保证。

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

PRIMARY KEY 约束自动定义了一个 UNIQUE 约束。

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table

请注意，每个表可以有多个 UNIQUE 约束，但每个表只能有一个 PRIMARY KEY 约束

回答by Thom Smith

A primary key is a minimal set of columns such that any two records with identical values in those columns have identical values in all columns. Note that a primary key can consist of multiple columns.

主键是一组最小的列，以便在这些列中具有相同值的任何两条记录在所有列中具有相同的值。请注意，主键可以由多列组成。

A uniqueness constraint is exactly what it sounds like.

唯一性约束正是它听起来的样子。