主键和唯一约束之间的区别是什么?

Someone asked me this question on an interview...

在一次面试中有人问了我这个问题……

16 个解决方案

#1

A primary key is a 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.

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

#2

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

主键不能为空。独特的钥匙。

#3

Primary key can not be null but unique can have only one null value.
主键不能为空，但惟一键只能有一个空值。
Primary key create the cluster index automatically but unique key not.
主键自动创建群集索引，但不创建唯一键。
A table can have only one primary key but unique key more than one.
一个表只能有一个主键，但唯一的键可以多于一个。

#4

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.

DR可以通过主键(惟一性、可引用性、非零性、聚类等)来表示，但是不能使用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 KEY is just the thing for you.

我建议，如果你是那种喜欢从……不用列出所有讨厌的列，主键就是你要的。

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可以有多个键，但我们只选择一个键作为下划线，并将其称为主键。选择是随意的，所以从逻辑的角度来看，主元素的概念并不是很重要。关键的一般概念是非常重要的!候选键的意思和key完全相同(例如。，候选人的加入并没有什么真正的意义——这是Ted Codd提出的，因为他把每一个关键作为被提名的主要关键)。SQL允许将表列的子集声明为该表的键。它还允许其中一个被指定为主键。在可能需要的其他约束中，将键指定为主键会带来一定的便利

What Is a Key? by Hugh Darwen

什么是关键?由休·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的任何其他键都被称为备用键)。但是，是否要选择一些关键因素作为主要因素，如果是的话，这些因素都是本质上的心理问题，超出了关系模型的范围。作为一个良好的实践，大多数基础relvars可能都应该有一个主键——但是，重复一遍，如果这是一个规则，那么这个规则实际上并不是一个关系问题……强烈推荐(对SQL用户):对于基本表，无论如何，使用主键和/或唯一规范来确保每个这样的表至少有一个键。

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

SQL和关系理论:如何编写精确的SQL代码

In standard SQL PRIMARY KEY

在标准的SQL主键中。

implies uniqueness but you can specify that explicitly (using UNIQUE).
表示惟一性，但可以显式地指定它(使用UNIQUE)。
implies NOT NULL but you can specify that explicitly when creating columns (but you should be avoiding nulls anyhow!)
表示NOT NULL，但您可以在创建列时显式地指定它(但您应该避免使用NULL !)
allows you to omit its columns in a FOREIGN KEY but 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).
每个表只能声明一个键，但是不清楚为什么(最初提出这个概念的Codd没有施加这样的限制)。

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

在某些产品中，主键暗示了表的聚集索引，但您可以显式地指定它(您可能不希望主键是聚集索引!)

For some people PRIMARY KEY has purely psychological significance:

对于一些人来说，主键有纯粹的心理意义:

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).
他们认为它表示将在外键中引用该键(这是Codd提出的，但标准SQL和SQL供应商实际上没有采用)。
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).
他们认为它暗示了一个“代理”或“人工”键，对业务没有任何意义(但实际上通过向用户公开，给企业带来了不必要的意义)。

#5

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.

假设你有一个表雇员，PK EmployeeID。例如，可以在SSN上添加唯一的约束。

#6

Unique Key constraints:

独特的键约束:

Unique key constraint will provide you a constraint like the column values should retain uniqueness.
唯一键约束将提供一个约束，就像列值应该保持惟一性一样。
It will create non-clustered index by default
默认情况下，它将创建非聚集索引
Any number of unique constraints can be added to a table.
可以将任意数量的惟一约束添加到表中。
It will allow null value in the column.

它将允许列中的空值。

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

添加约束UNIQUE_CONSTRAINT UNIQUE (column_name1, column_name2，…)

Primary Key:

主键:

Primary key will create column data uniqueness in the table.
主键将在表中创建列数据惟一性。
Primary key will create clustered index by default
主键将在默认情况下创建聚集索引
Only one Primay key can be created for a table
只能为表创建一个Primay键
Multiple columns can be consolidated to form a single primary key
可以合并多个列以形成一个主键。
It wont allow null values.

它不允许空值。

ALTER TABLE table_name ADD CONSTRAINT KEY_CONSTRAINT PRIMARY KEY (column_name)

ALTER TABLE table_name添加约束KEY_CONSTRAINT主键(column_name)

#7

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

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

#8

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.
主键的目的是惟一地标识表中的一行。唯一约束确保字段的值在表中的行中是唯一的。
You can have only one primary key per table. You can have more than one unique constraint per table.
每个表只能有一个主键。每个表可以有多个唯一的约束。

#9

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.

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

#10

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

惟一约束惟一地标识数据库表中的每个记录。

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

惟一和主键约束都为列或列集的惟一性提供了保证。

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

主键约束会自动定义唯一的约束。

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

注意，每个表可以有许多唯一的约束，但每个表只有一个主键约束。

#11

Primary key can't be null but unique constraint is nullable. when you choose a primary key for your table it's atomatically Index that field.

主键不能为空，但唯一的约束是可空的。当您为表选择主键时，它会自动索引该字段。

#12

Primary keys are essentially combination of (unique +not null). also when referencing a foreign key the rdbms requires Primary key.

主键本质上是(唯一+非空)的组合。在引用外键时，rdbms还需要主键。

Unique key just imposes uniqueness of the column.A value of field can be NULL in case of uniqe key. Also it cannot be used to reference a foreign key, that is quite obvious as u can have null values in it

惟一键只强制要求列具有惟一性。如果使用uniqe键，字段值可以为NULL。它也不能用于引用外键，这很明显，因为u中可以有空值

#13

Both guarantee uniqueness across the rows in the table, with the exception of nulls as mentioned in some of the other answers.

这两种方法都保证了表中所有行的惟一性，除了在其他一些答案中提到的null之外。

Additionally, a primary key "comes with" an index, which could be either clustered or non-clustered.

此外，主键“附带”一个索引，该索引可以是集群的，也可以是非集群的。

#14

There are several good answers in here so far. In addition to the fact that a primary key can't be null, is a unique constraint itself, and can be comprised of multiple columns, there are deeper meanings that depend on the database server you are using.

到目前为止，这里有几个很好的答案。除了一个主键不能为空的事实之外，它本身也是一个惟一的约束，并且可以由多个列组成，有更深层的含义取决于您正在使用的数据库服务器。

I am a SQL Server user, so a primary key has a more specific meaning to me. In SQL Server, by default, primary keys are also part of what is called the "clustered index". A clustered index defines the actual ordering of data pages for that particular table, which means that the primary key ordering matches the physical order of rows on disk.

我是SQL Server用户，所以主键对我有更具体的含义。在SQL Server中，默认情况下，主键也是所谓的“集群索引”的一部分。集群索引定义特定表的数据页的实际排序，这意味着主键排序与磁盘上的行的物理排序相匹配。

I know that one, possibly more, of MySql's table formats also support clustered indexing, which means the same thing as it does in SQL Server...it defines the physical row ordering on disk.

我知道，MySql的表格式也支持集群化索引，这与在SQL Server中做的一样……它定义了磁盘上的物理行排序。

Oracle provides something called Index Organized Tables, which order the rows on disk by the primary key.

Oracle提供了所谓的索引组织表，它通过主键对磁盘上的行进行排序。

I am not very familiar with DB2, however I think a clustered index means the rows on disk are stored in the same order as a separate index. I don't know if the clustered index must match the primary key, or if it can be a distinct index.

我对DB2不是很熟悉，但是我认为集群索引意味着磁盘上的行以与单独索引相同的顺序存储。我不知道聚集索引是否必须匹配主键，或者它是否可以是一个不同的索引。

#15

A great number of the answers here have discussed the properties of PK vs unique constraints. But it is more important to understand the difference in concept.

这里有大量的答案讨论了PK与唯一约束的性质。但更重要的是理解概念上的差异。

A primary key is considered to be an identifier of the record in the database. So these will for example be referenced when creating foreign key references between tables. A primary key should therefore under normal circumstances never contain values that have any meaining in your domain (often automatically incremential fields are used for this).

主键被认为是数据库中记录的标识符。例如，当在表之间创建外键引用时，这些将被引用。因此，在正常情况下，主键不应该包含在域中具有任何meaining的值(通常会自动地使用递增域字段)。

A unique constraint is just a way of enforcing domain specific business rules in your database schema.

唯一约束是在数据库模式中强制执行特定于域的业务规则的一种方式。

Becuase a PK it is an identifier for the record, you can never change the value of a primary key.

因为PK是记录的标识符，所以不能更改主键的值。

#16

create table a1 (id int,name varchar(20),city varchar(20),state varchar(20),country varchar(28) constraint pk_a1 primary key(id));
insert into a1 values(101,'sohan','gkp','up','india');


create table a1 (id int primary key,name varchar(20),city varchar(20),state varchar(20),country varchar(28));
insert into a1 values(101,'sohan','gkp','up','india');

what is the diffrence between these two tables using primary key and using constraints

使用主键和使用约束的这两个表之间的差异是什么

#1