MySQL 主键和唯一键的区别

difference between primary key and unique key

提问by Anuj

I'm using mysql database. I have a confusion between primary key and unique key.

我正在使用 mysql 数据库。我在主键和唯一键之间存在混淆。

Please help me where should I create primary and unique key. I mean in which situation we create unique key or primary key .

请帮我在哪里创建主键和唯一键。我的意思是在哪种情况下我们创建 unique key 或 primary key 。

采纳答案by Mr. KB

Primary Key:

首要的关键：

• There can only be one primary key in a table
• In some DBMS it cannot be NULL- e.g. MySQL adds NOT NULL
• Primary Key is a unique key identifier of the record

• 一张表只能有一个主键
• 在某些 DBMS 中它不能NULL- 例如 MySQL 添加NOT NULL
• 主键是记录的唯一键标识符

Unique Key:

唯一键：

• Can be more than one unique key in one table
• Unique key can have NULLvalues
• It can be a candidate key
• Unique key can be NULL; multiple rows can have NULLvalues and therefore may not be considered "unique"

• 一张表中可以有多个唯一键
• 唯一键可以有NULL值
• 它可以是候选键
• 唯一键可以是NULL；多行可以有NULL值，因此可能不被认为是“唯一的”

回答by dhi

Unique Key (UK): It's a column or a group of columns that can identify a uniqueness in a row.

唯一键（英国）：它是可以标识行中唯一性的一列或一组列。

Primary Key (PK): It's alsoa column or group of columns that can identify a uniqueness in a row.

主键 (PK)：它也是可以标识行中唯一性的一列或一组列。

So the Primary key is just another name for unique key, but the default implementation in SQL Server is different for Primary and Unique Key.

所以主键只是唯一键的另一个名称，但 SQL Server 中的默认实现对于主键和唯一键是不同的。

By Default:

默认情况下：

1. PK creates a Clustered index and UK creates a Non Clustered Index.
2. PK is not null, but UK allows nulls (Note: By Default)
3. There can only be one and only one PK on a table, but there can be multiple UK's
4. You can override the default implementation depending upon your need.

1. PK 创建一个聚集索引，UK 创建一个非聚集索引。
2. PK 不为空，但 UK 允许为空（注意：默认情况下）
3. 一张桌子上只能有一个也只有一个PK，但可以有多个UK的
4. 您可以根据需要覆盖默认实现。

It really depends what is your aim when deciding whether to create a UK or PK. It follows an analogy like "If there is a team of three people, so all of them are peers, but there will be one of them who will be a pair of peers: PK and UK has similar relation.". I would suggest reading this article: The example given by the author may not seem suitable, but try to get an overall idea.

在决定是创建 UK 还是 PK 时，这实际上取决于您的目标是什么。它遵循一个类比，“如果有一个由三个人组成的团队，那么他们都是peer，但会有一个是一对peer：PK和UK有相似的关系。”。我建议阅读这篇文章：作者给出的例子可能看起来不合适，但试着得到一个整体的想法。

http://tsqltips.blogspot.com/2012/06/difference-between-unique-key-and.html

http://tsqltips.blogspot.com/2012/06/difference-between-unique-key-and.html

回答by nayeemDotNetAuthorities

For an organization or a business, there are so many physical entities (such as people, resources, machines, etc.) and virtual entities (their Tasks, transactions, activities). Typically, business needs to record and process information of those business entities. These business entities are identified within a whole business domain by a Key.

对于一个组织或一个企业来说，有太多的物理实体（如人、资源、机器等）和虚拟实体（他们的任务、交易、活动）。通常，业务需要记录和处理这些业务实体的信息。这些业务实体在整个业务领域内由一个 Key 标识。

As per RDBMS prospective, Key (a.k.a Candidate Key) is a value or set of values that uniquely identifies an entity.

根据 RDBMS 的预期，键（又名候选键）是唯一标识实体的一个值或一组值。

For a DB-Table, there are so many keys are exist and might be eligible for Primary Key. So that all keys, primary key, unique key, etc are collectively called as Candidate Key. However, DBA selected a key from candidate key for searching records is called Primary key.

对于 DB-Table，存在很多键并且可能有资格作为主键。这样所有的键、主键、唯一键等统称为候选键。但是，DBA 从候选键中选择一个用于搜索记录的键称为主键。

Difference between Primary Key and Unique key

主键和唯一键的区别

1. Behavior:Primary Key is used to identify a row (record) in a table, whereas Unique-key is to prevent duplicate values in a column (with the exception of a null entry).

1.行为：Primary Key用于标识表中的一行（记录），而Unique-key是为了防止列中出现重复值（空条目除外）。

2. Indexing:By default SQL-engine creates Clustered Index on primary-key if not exists and Non-Clustered Index on Unique-key.

2. 索引：默认情况下，SQL 引擎在主键上创建聚集索引（如果不存在）和非聚集索引在唯一键上。

3. Nullability:Primary key does not include Null values, whereas Unique-key can.

3. 可空性：主键不包含空值，而唯一键可以。

4. Existence:A table can have at most one primary key, but can have multiple Unique-key.

4. 存在性：一张表最多只能有一个主键，但可以有多个唯一键。

5. Modifiability:You can't change or delete primary values, but Unique-key values can.

5. 可修改性：您不能更改或删除主值，但唯一键值可以。

For more information and Examples:

有关更多信息和示例：

http://dotnetauthorities.blogspot.in/2013/11/Microsoft-SQL-Server-Training-Online-Learning-Classes-Integrity-Constraints-PrimaryKey-Unique-Key_27.html

http://dotnetauthorities.blogspot.in/2013/11/Microsoft-SQL-Server-Training-Online-Learning-Classes-Integrity-Constraints-PrimaryKey-Unique-Key_27.html

回答by Oded

A primary key must be unique.

主键必须是唯一的。

A unique key does not have to be the primary key - see candidate key.

唯一键不必是主键 - 请参阅候选键。

That is, there may be more than one combination of columns on a table that can uniquely identify a row - only one of these can be selected as the primary key. The others, though unique are candidate keys.

也就是说，一张表上可能有多个列组合可以唯一标识一行——只能选择其中一个作为主键。其他的虽然是唯一的，但都是候选键。

回答by Omer K

Difference between Primary Key and Unique Key

+-----------------------------------------+-----------------------------------------------+
|                Primary Key              |                    Unique Key                 |
+-----------------------------------------+-----------------------------------------------+
| Primary Key can't accept null values.   | Unique key can accept only one null value.    |
+-----------------------------------------+-----------------------------------------------+
| By default, Primary key is clustered    | By default, Unique key is a unique            |
| index and data in the database table is | non-clustered index.                          |
| physically organized in the sequence of |                                               |
| clustered index.                        |                                               |
+-----------------------------------------+-----------------------------------------------+
| We can have only one Primary key in a   | We can have more than one unique key in a     |
| table.                                  | table.                                        |
+-----------------------------------------+-----------------------------------------------+
| Primary key can be made foreign key     | In SQL Server, Unique key can be made foreign |
| into another table.                     | key into another table.                       |
+-----------------------------------------+-----------------------------------------------+

主键和唯一键的区别

+-----------------------------------------+-----------------------------------------------+
|                Primary Key              |                    Unique Key                 |
+-----------------------------------------+-----------------------------------------------+
| Primary Key can't accept null values.   | Unique key can accept only one null value.    |
+-----------------------------------------+-----------------------------------------------+
| By default, Primary key is clustered    | By default, Unique key is a unique            |
| index and data in the database table is | non-clustered index.                          |
| physically organized in the sequence of |                                               |
| clustered index.                        |                                               |
+-----------------------------------------+-----------------------------------------------+
| We can have only one Primary key in a   | We can have more than one unique key in a     |
| table.                                  | table.                                        |
+-----------------------------------------+-----------------------------------------------+
| Primary key can be made foreign key     | In SQL Server, Unique key can be made foreign |
| into another table.                     | key into another table.                       |
+-----------------------------------------+-----------------------------------------------+

You can find detailed information from:
http://www.dotnet-tricks.com/Tutorial/sqlserver/V2bS260912-Difference-between-Primary-Key-and-Unique-Key.html

您可以从以下位置找到详细信息：http:
//www.dotnet-tricks.com/Tutorial/sqlserver/V2bS260912-Difference-between-Primary-Key-and-Unique-Key.html

回答by Jens Schauder

A primary key has the semantic of identifying the row of a database. Therefore there can be only one primary key for a given table, while there can be many unique keys.

主键具有标识数据库行的语义。因此，给定的表只能有一个主键，而可以有许多唯一键。

Also for the same reason a primary key cannot be NULL (at least in Oracle, not sure about other databases)

同样出于同样的原因，主键不能为 NULL（至少在 Oracle 中，不确定其他数据库）

Since it identifies the row it should never ever change. Changing primary keys are bound to cause serious pain and probably eternal damnation.

因为它标识了它永远不应该改变的行。更改主键必然会导致严重的痛苦，甚至可能是永恒的诅咒。

Therefor in most cases you want some artificial id for primary key which isn't used for anything but identifying single rows in the table.

因此，在大多数情况下，您需要一些主键的人工 id，它除了标识表中的单行外不用于任何其他用途。

Unique keys on the other hand may change as much as you want.

另一方面，唯一键可能会根据您的需要进行更改。

回答by Buhake Sindi

A Primary key is a unique key.

主键是唯一键。

Each table must have at mostONE primary key but it can have multiple unique key. A primary key is used to uniquely identify a table row. A primary key cannot be NULLsince NULLis not a value.

每个表最多只能有一个主键，但可以有多个唯一键。主键用于唯一标识表行。主键不能是，NULL因为NULL它不是值。

回答by Mahedi Hasan Durjoy

• Think the table name is employe.
• Primary key
• Primary key can not accept null values. primary key enforces uniqueness of a column. We can have only one Primary key in a table.
• Unique key
• Unique key can accept null values. unique key also enforces uniqueness of a column.you can think if unique key contains null values then why it can be unique ? yes, though it can accept null values it enforces uniqueness of a column. just have a look on the picture.here Emp_ID is primary and Citizen ID is unique. Hope you understand. We can use multiple unique key in a table.

• 认为表名是雇员。
• 首要的关键
• 主键不能接受空值。主键强制列的唯一性。一张表中只能有一个主键。
• 唯一键
• 唯一键可以接受空值。唯一键还强制列的唯一性。您可以考虑如果唯一键包含空值，那么为什么它可以是唯一的？是的，虽然它可以接受空值，但它强制执行列的唯一性。看看图片。这里 Emp_ID 是主要的，而 Citizen ID 是唯一的。希望你能理解。我们可以在一个表中使用多个唯一键。

回答by Manuri Perera

I know this question is several years old but I'd like to provide an answer to this explaining why rather than how

我知道这个问题已经有好几年了，但我想对此提供一个答案，解释为什么而不是如何

Purpose of Primary Key: To identify a row in a database uniquely => A row represents a single instance of the entity type modeled by the table. A primary key enforces integrity of an entity, AKA Entity Integrity. Primary Key would be a clustered index i.e. it defines the order in which data is physically stored in a table.

主键的目的：唯一标识数据库中的一行 => 一行表示由表建模的实体类型的单个实例。主键强制执行实体的完整性，也就是实体完整性。主键将是一个聚集索引，即它定义了数据物理存储在表中的顺序。

Purpose of Unique Key: Ok, with the Primary Key we have a way to uniquely identify a row. But I have a business need such that, another column/a set of columns should have unique values. Well, technically, given that this column(s) is unique, it can be a candidate to enforce entity integrity. But for all we know, this column can contain data originating from an external organization that I may have a doubt about being unique. I may not trust it to provide entity integrity. I just make it a unique key to fulfill my business requirement.

唯一键的目的：好的，有了主键，我们就有了唯一标识一行的方法。但我有一个业务需求，另一列/一组列应该具有唯一值。好吧，从技术上讲，鉴于此列是唯一的，它可以成为强制执行实体完整性的候选者。但就我们所知，该列可能包含来自外部组织的数据，我可能怀疑其是否独一无二。我可能不相信它提供实体完整性。我只是让它成为满足我的业务需求的唯一键。

There you go!

你去吧！

回答by ayushs27

Unique key :- It should be used when you have to give unique value.In the case of unique key it means null values are also allowed.Unique keys are those keys which are unique and non similar in that column like for example your pet name.it can be nothing like null and if you are asking in context of database then it must be noted that every null is different from another null in the database.EXCEPT-SQL Server where null=null is true

唯一键 :- 当您必须提供唯一值时应该使用它。在唯一键的情况下，这意味着也允许空值。唯一键是那些在该列中唯一且不相似的键，例如您的宠物名.它不能像空值一样，如果你在数据库的上下文中询问，那么必须注意每个空值都不同于数据库中的另一个空值。除了 null=null 为真的 SQL Server

---

---

primary key :- It should be used when you have to give uniquely identify a row.primary is key which unique for every row in a database constraint is that it doesn't allow null in it.so, you might have seen that the database have a column which is auto increment and it is the primary key of the table. plus it can be used as a foreign key in another table.example can be orderId on a order Table,billId in a bill Table.

now coming back to situation when to use it:-

主键 :- 当您必须唯一标识行时应该使用它。主键是数据库约束中每一行唯一的键，因为它不允许在其中包含空值。所以，您可能已经看到数据库有一列是自动递增的，它是表的主键。加上它可以用作另一个表中的外键。示例可以是订单表中的 orderId，账单表中的 billId。

现在回到何时使用它的情况：-

1)primary key in the column which can not be null in the table and you are using as foreign key in another table for creating relationship

1）列中的主键在表中不能为空，并且您在另一个表中用作外键以创建关系

2) unique key in table where it doesn't affect in table or in the whole database whether you take the null for the particular column like snacks in the restaurant it is possible you don't take snacks in a restaurant

2）表中的唯一键，它不会影响表中或整个数据库中是否为特定列（如餐厅的小吃）取空值，您可能不在餐厅吃小吃