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

What is the difference between Primary key And unique Key constraint?

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

What's the use of it??

有什么用？

6 个解决方案

#1

Both are used to denote candidate keys for a table.

两者都用于表示表的候选键。

You can only have one primary key for a table so would just need to pick one if you have multiple candidates.

您只能为表创建一个主键，因此如果您有多个候选项，则只需选择一个主键。

Either can be used in Foreign Key constraints. In SQL Server the Primary Key columns cannot be nullable. Columns used in Unique Key constraints can be.

可以在外键约束中使用。在SQL Server中，主键列不能为空。 Unique Key约束中使用的列可以是。

By default in SQL Server the Primary Key will become the clustered index if it is created on a heap but it is by no means mandatory that the PK and clustered index should be the same.

默认情况下，在SQL Server中，如果主键在堆上创建，则主键将成为聚簇索引，但PK和聚簇索引应该是相同的，这绝不是强制性的。

#2

A primary key is one which is used to identify the row in question. It might also have some meaning beyond that (if there was already a piece of "real" data that could serve) or it may be purely an implementation artefact (most IDENTITY columns, and equivalent auto-incremented values on other database systems).

主键是用于标识所讨论的行的键。除此之外它还可能有一些含义（如果已经存在一段可以提供服务的“真实”数据），或者它可能纯粹是一个实现工件（大多数IDENTITY列，以及其他数据库系统上的等效自动递增值）。

A unique key is a more general case, where a key cannot have repeated values. In most cases people cannot have the same social security numbers in relation to the same jurisdiction (an international case could differ). Hence if we were storing social security numbers, then we would want to model them as unique, as any case of them matching an existing number is clearly wrong. Usernames generally must be unique also, so here's another case. External identifiers (identifiers used by another system, standard or protocol) tend to also be unique, e.g. there is only one language that has a given ISO 639 code, so if we were storing ISO 639 codes we would model that as unique.

唯一键是更一般的情况，其中键不能具有重复值。在大多数情况下，人们不能拥有与同一管辖区相同的社会安全号码（国际案例可能不同）。因此，如果我们存储社会安全号码，那么我们希望将它们建模为唯一的，因为它们匹配现有数字的任何情况都显然是错误的。用户名通常也必须是唯一的，所以这是另一种情况。外部标识符（由另一系统，标准或协议使用的标识符）往往也是唯一的，例如，只有一种语言具有给定的ISO 639代码，因此如果我们存储ISO 639代码，我们会将其建模为唯一。

This uniqueness can also be across more than one column. For example, in most hierarchical categorisation systems (e.g. a folder structure) no item can have both the same parent item and the same name, though there could be other items with the same parent and different names, and others with the same name and different parents. This multi-column capability is also present on primary keys.

这种独特性也可以跨越多个列。例如，在大多数分层分类系统（例如文件夹结构）中，没有项目可以具有相同的父项和相同的名称，尽管可能存在具有相同父项和不同名称的其他项，以及具有相同名称和不同名称的其他项。父母。这种多列功能也存在于主键上。

A table may also have more than one unique key. E.g. a user may have both an id number and a username, and both will need to be unique.

表还可以具有多个唯一键。例如。用户可能同时拥有ID号和用户名，两者都需要是唯一的。

Any non-nullable unique key can therefore serve as a primary key. Sometimes primary keys that come from the innate data being modelled are referred to as "natural primary keys", because they are a "natural" part of the data, rather than just an implementation artefact. The decision as to which to use depends on a few things:

因此，任何不可空的唯一键都可以用作主键。有时，来自建模的固有数据的主键被称为“自然主键”，因为它们是数据的“自然”部分，而不仅仅是实现假象。决定使用哪个取决于以下几点：

1. Likelihood of change of specification. If we modelled a social security number as unique and then had to adapt to allow for multiple jurisdictions where two or more use a similar enough numbering system to allow for collisions, we likely need just remove the uniqueness constraint (other changes may be needed). If it was our primary key, we now also need to use a new primary key, and change any table that was using that primary key as part of a relationship, and any query that joined on it.

   规格变更的可能性。如果我们将社会安全号码建模为唯一，然后必须适应允许多个管辖区域，其中两个或更多使用类似的足够编号系统以允许冲突，我们可能只需要删除唯一性约束（可能需要其他更改）。如果它是我们的主键，我们现在还需要使用新的主键，并将任何使用该主键的表更改为关系的一部分，以及任何加入它的查询。

2. Speed of look-up. Key efficiency can be important, as they are used in many WHERE clauses and (more often) in many JOINs. With JOINS in particular, speed of lookup can be vital. The impact will depend on implementation details, and different databases vary according to how they will handle different datatypes (I would have few qualms from a performance perspective in using a large piece of text as a primary key in Postgres where I could specify the use of hash joins, but I'd be very hesitant to do so in SQLServer [Edit: for "large" I'm thinking of perhaps the size of a username, not something the size of the entire Norse Eddas!]).

   查找速度。关键效率可能很重要，因为它们在许多WHERE子句中使用，并且（更常见地）在许多JOIN中使用。特别是JOINS，查找速度至关重要。影响将取决于实现细节，不同的数据库根据它们处理不同数据类型的方式而有所不同（从性能角度来看，在Postgres中使用大块文本作为主键我可以指定使用hash加入，但是我在SQLServer中非常犹豫[编辑：对于“大”我想的可能是用户名的大小，而不是整个挪威语Eddas的大小！]）。

3. Frequency of the key being the only interesting data. For example, with a table of languages, and a table of pieces of comments in that language, very often the only reason I would want to join on the language table when dealing with the comments table is either to obtain the language code or to restrict a query to those with a particular language code. Other information about the language is likely to be much more rarely used. In this case while joining on the code is likely to be less efficient than joining on a numeric id set from an IDENTITY column, having the code as the primary key - and hence as what is stored in the foreign key column on the comments table - will remove the need for any JOIN at all, with a considerable efficiency gain. More often though I want more information from the relevant tables than that, so making the JOIN more efficient is more important.

   密钥的频率是唯一有趣的数据。例如，使用语言表和该语言的注释表，在处理注释表时，我想要在语言表上加入的唯一原因是获取语言代码或限制查询具有特定语言代码的人。关于该语言的其他信息可能更少使用。在这种情况下，加入代码的效率可能低于加入IDENTITY列中的数字ID集，将代码作为主键 - 因此存储在注释表的外键列中 - 将完全取消任何JOIN的需要，并获得相当大的效率。虽然我想从相关表中获得更多信息，但更常见的是，使JOIN更高效更为重要。

#3

Primary key:

首要的关键：

1. Primary key is nothing but it uniquely identifies each row in a table.

   主键只是它唯一标识表中的每一行。

2. Primary key does not allow duplicate values, nor NULL.

   主键不允许重复值，也不允许NULL。

3. Primary key by default is a clustered index.

   默认情况下，主键是聚簇索引。

4. A table can have only one primary key.

   一个表只能有一个主键。

Unique Key:

独特的关键：

1. Unique key is nothing but it uniquely identifies each row in a table.

   唯一键只是它唯一标识表中的每一行。

2. Unique key does not allow duplicate values, but it allows (at most one) NULL.

   唯一键不允许重复值，但它允许（最多一个）NULL。

3. Unique key by default is a non-clustered index.

   默认情况下，唯一键是非聚集索引。

This is a fruit full link to understand the Primary Key Database Keys. Keep in mind we have only one clustered index in a table [Talking about SQL Server 2005]. Now if we want to add another unique column then we will use Unique Key column, because Unique Key column can be added more than one.

这是一个了解主键数据库键的完整水果链接。请记住，表中只有一个聚簇索引[谈论SQL Server 2005]。现在，如果我们想要添加另一个唯一列，那么我们将使用Unique Key列，因为可以添加多个Unique Key列。

#4

A primary key is just any one candidate key. In principle primary keys are not different from any other candidate key because all keys are equal in the relational model.

主键只是任何一个候选键。原则上，主键与任何其他候选键没有区别，因为关系模型中的所有键都相同。

SQL however has two different syntax for implementing candidate keys: the PRIMARY KEY constraint and the UNIQUE constraint (on non-nullable columns of course). In practice they achieve exactly the same thing except for the essentially useless restriction that a PRIMARY KEY can only be used once per table whereas a UNIQUE constraint can be used multiple times.

然而，SQL有两种不同的语法来实现候选键：PRIMARY KEY约束和UNIQUE约束（当然，在非可空列上）。在实践中，除了基本无用的限制之外，它们实现了完全相同的限制，即PRIMARY KEY每个表只能使用一次，而UNIQUE约束可以多次使用。

So there is no fundamental "use" for the PRIMARY KEY constraint. It is redundant and could easily be ignored or dropped from the language altogether. However, many people find it convenient to single out one particular key per table as having special significance. There is a very widely observed convention that keys designated with PRIMARY KEY are used for foreign key references, although this is entirely optional.

因此PRIMARY KEY约束没有基本的“使用”。它是多余的，很容易被忽略或完全从语言中删除。然而，许多人发现将每个表中的一个特定密钥单独输出具有特殊意义是很方便的。有一个非常广泛观察到的约定，用PRIMARY KEY指定的密钥用于外键引用，尽管这完全是可选的。

#5

Short version:

简洁版本：

• From the point of view of database theory, there is none. Both are simply candidate keys.
• 从数据库理论的角度来看，没有。两者都只是候选键。
• In practice, most DMBS like to have one "standard key", which can be used for e.g. deciding how to store data, and to tell tools and DB clients which is the best way to identify a record.
• 在实践中，大多数DMBS喜欢有一个“标准密钥”，它可以用于例如决定如何存储数据，并告诉工具和数据库客户端这是识别记录的最佳方法。

So distinguishing one unique key as the "primary key" is just an implementation convenience (but an important one).

因此，区分一个唯一密钥作为“主键”只是实现方便（但是重要的一个）。

#6

Both primary key and unique key are used to enforce, the uniqueness of a column. So, when do you choose one over the other?

主键和唯一键都用于强制列的唯一性。那么，你何时选择一个而不是另一个？

A table can have, only one primary key. If you want to enforce uniqueness on 2 or more columns, then we use unique key constraint.

一个表只能有一个主键。如果要在2列或更多列上强制实施唯一性，那么我们使用唯一键约束。

Difference between Primary key constraint and Unique key constraint?

主键约束和唯一键约束之间的区别？

1. A table can have only one primary key, but more than one unique key

1.一个表只能有一个主键，但只能有一个以上的唯一键

2. Primary key does not allow nulls, where as unique key allows one null

2.主键不允许空值，其中唯一键允许空值

ALTER TABLE Table_Name
ADD CONSTRAINT Constraint_Name PRIMARY KEY (Column_Name)

Alter Table Table_Name
Add Constraint Constraint_Name Unique(Column_Name)