唯一键和主键之间的区别

I came across the following SQL in a book:

我在一本书中发现了以下SQL:

CREATE TABLE 'categories'(
id SMALLINT NOT NULL AUTO INCREMENT,
category VARCHAR(30) NOT NULL,
PRIMARY KEY('id'),
UNIQUE KEY 'category'('category')
)ENGINE=MyISAM DEFAULT CHARSET = utf8;

I was wondering is there a reason why I would need a PRIMARY and UNIQUE KEY in the same table? I guess, underlying that question is, what is the difference between PRIMARY and UNIQUE keys?

我想知道为什么我需要一个主键和唯一键在同一个表中?我想，潜在的问题是，主键和唯一键的区别是什么?

5 个解决方案

#1

The relational model says there's no essential difference between one key and another. That is, when a relation has more than one candidate key, there are no theoretical reasons for declaring that this key is more important than that key. Essentially, that means there's no theoretical reason for identifying one key as a primary key, and all the others as secondary keys. (There might be practical reasons, though.)

关系模型说，一个键和另一个键之间没有本质的区别。也就是说，当一个关系有多个候选键时，理论上没有理由说这个键比那个键更重要。从本质上讲，这意味着没有理论理由将一个键确定为主键，而将所有其他键确定为辅助键。(不过，这可能有实际的原因。)

Many relations have more than one candidate key. For example, a relation of US states might have data like this.

许多关系都有不止一个候选密钥。例如，美国国家的关系可能有这样的数据。

State      Abbr      Postal Code
--
Alabama    Ala.      AL
Alaska     Alaska    AK
Arizona    Ariz.     AZ
...
Wyoming    Wyo.      WY

It's clear that values in each of those three columns are unique--there are three candidate keys.

很明显，这三列中的每个值都是惟一的——有三个候选键。

If you were going to build a table in SQL to store those values, you might do it like this.

如果要在SQL中构建一个表来存储这些值，可以这样做。

CREATE TABLE states (
  state varchar(15) primary key,
  abbr varchar(10) not null unique,
  postal_code char(2) not null unique
);

And you'd do something like that because SQL doesn't have any other way to say "My table has three separate candidate keys."

你可以这样做，因为SQL没有其他的方法来表示“我的表有三个独立的候选键”

I didn't have any particular reason for choosing "state" as the primary key. I could have just as easily chosen "abbr" or "postal_code". Any of those three columns can be used as the target for a foreign key reference, too.

我没有特别的理由选择“state”作为主键。我也可以简单地选择“abbr”或“postal_code”。这三列中的任何一个都可以作为外键引用的目标。

And as far as that goes, I could have built the table like this, too.

就这样，我也可以像这样构建表格。

CREATE TABLE states (
  state varchar(15) not null unique,
  abbr varchar(10) not null unique,
  postal_code char(2) not null unique
);

#2

I'm surprised that nobody mentionned that a primary key can be referenced as foreign key into other tables.

我很惊讶，没有人提到一个主键可以作为外键引用到其他表中。

Also an unique constraint allows NULL values.

唯一约束也允许空值。

#3

The reason you need two uniqueness restrictions (one being the Primary Key) is that you are using Id as a surrogate key. I.e., it is an arbitrary value that has no meaning in relation to the data itself. Without the unique key (or colloquially known as "business key" i.e, a key that the user would recognize as being enforced), a user could add two identical category values with different arbitrary Id values. Since users should never see the surrogate key, they would not know why they are seeing a duplicate even though the database would think they are different.

您需要两个惟一限制(一个是主键)的原因是，您正在使用Id作为代理键。即。，它是一个与数据本身无关的任意值。没有唯一的键(或通俗地称为“业务键”i)。用户可以添加两个具有不同任意Id值的相同类别值。因为用户永远不会看到代理键，所以即使数据库认为它们是不同的，他们也不知道为什么会看到副本。

When using surrogate keys, having another unique constraint on something other than the surrogate key is critical to avoid duplicate data.

在使用代理键时，对除代理键之外的其他内容有另一个惟一的约束对于避免重复数据至关重要。

Depending on who you talk to and how they read the specification, Unique keys( which is redundant by the way. A "key" is by definition unique) are also not supposed to allow nulls. However, one can also read the specifications as saying that Unique constraints, unlike Primary Key constraints, are in fact supposed to allow nulls (how many nulls are allowed also varies by vendor). Most products, including MySQL, do allow nulls in Unique constraints whereas Primary Key constraints do not.

取决于您与谁交谈，以及他们如何阅读规范，唯一键(顺便说一下，这是多余的)。根据定义，“键”是唯一的)也不允许为空。但是，您还可以阅读规范，认为与主键约束不同的是，惟一的约束实际上是允许nulls(允许有多少个空值也因供应商而异)。大多数产品(包括MySQL)都允许在唯一的约束条件下进行空值，而主键约束则不允许。

#4

Similarity

相似

Both a PRIMARY and UNIQUE index create a constraint that requires all values to be distinct (1).

主索引和惟一索引都创建一个约束，该约束要求所有值都是不同的(1)。

Difference

区别

The PRIMARY key (implicitly) defines all key columns as NOT NULL; additionally, a table can only have one primary key.

主键(隐式)将所有键列定义为非空;另外，一个表只能有一个主键。

(1) Each NULL value is considered to be distinct.

(1)每个空值被认为是不同的。

#5

A UNIQUE constraint and PRIMARY key both are similar and it provide unique enforce uniqueness of the column on which they are defined. Some are basic differences between Primary Key and Unique key are as follows.

唯一约束和主键都是相似的，并且它为定义它们的列提供惟一的强制惟一性。主键和唯一键之间的一些基本区别如下。

Primary key

主键

Primary key cannot have a NULL value. Each table can have only single primary key. Primary key is implemented as indexes on the table. By default this index is clustered index. Primary key can be related with another table's as a Foreign Key. We can generate ID automatically with the help of Auto Increment field. Primary key supports Auto Increment value.

主键不能有空值。每个表只能有一个主键。主键作为表上的索引实现。默认情况下，该索引是群集索引。主键可以作为外键与另一个表相关联。利用自动增量字段自动生成ID。主键支持自动增值。

Unique Constraint

唯一约束

Unique Constraint may have a NULL value. Each table can have more than one Unique Constraint. Unique Constraint is also implemented as indexes on the table. By default this index is Non-clustered index. Unique Constraint cannot be related with another table's as a Foreign Key. Unique Constraint doesn't support Auto Increment value.

唯一的约束可能有一个空值。每个表可以有多个唯一约束。惟一约束也被实现为表上的索引。默认情况下，这个索引是非聚集索引。唯一约束不能作为外键与另一个表关联。唯一约束不支持自动增量值。

You can find detailed information from: http://www.oracleinformation.com/2014/04/difference-between-primary-key-and-unique-key.html

您可以从http://www.oracleinformation.com/2014/04/difference- -primary-key- unique-key -key.html找到详细信息

#1