主键和唯一键的区别。

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 .

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

12 个解决方案

#1

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
• 在某些DBMS中，它不能为NULL——例如，MySQL添加了NOT NULL
• Primary Key is a unique key identifier of the record
• 主键是记录的唯一键标识符

Unique Key:

独特的关键:

• Can be more than one unique key in one table
• 一个表中可以有多个唯一键吗
• Unique key can have NULL values
• 唯一键可以有空值
• It can be a candidate key
• 它可以是候选键
• Unique key can be NULL and may not be unique
• 唯一键可以是NULL，也可以不是唯一的

#2

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

Unique Key (UK):它是一列或一组列，可以标识行中的唯一性。

Primary Key (PK): It's also a 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创建集群索引，UK创建非集群索引。
3. PK is not null, but UK allows nulls (Note: By Default)
4. PK不是null，但是UK允许null(注意:默认情况下)
5. There can only be one and only one PK on a table, but there can be multiple UK's
6. 一个表上只能有一个PK，但可以有多个UK
7. You can override the default implementation depending upon your need.
8. 可以根据需要覆盖默认实现。

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.

它真的取决于你的目标是什么在决定是否创建一个英国或PK,它遵循一个比喻就像“如果有一组三个人,所以他们都是同龄人,但会有一个人将一对同行:PK和英国也有类似的关系。”。我建议阅读这篇文章:作者给出的例子可能不太合适，但试着获得一个整体的想法。

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

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

#3

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.

对于组织或企业来说，有很多物理实体(如人员、资源、机器等)和虚拟实体(它们的任务、事务、活动)。通常，业务需要记录和处理这些业务实体的信息。这些业务实体通过一个键在整个业务域中进行标识。

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

根据RDBMS预期，Key (a.k。候选键)是唯一标识实体的值或值集。

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。行为:主键用于标识表中的一行(记录)，而惟一键用于防止列中的重复值(null条目除外)。

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

2。索引:默认情况下，SQL-engine在主键上创建聚集索引(如果不存在的话)，在惟一键上创建非聚集索引。

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

#4

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.

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

#5

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.

另一方面，唯一的键可以随着你的需要而改变。

#6

A Primary key is a unique key.

主键是唯一的键。

Each table must have at most ONE 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 NULL since NULL is not a value.

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

#7

Difference between Primary Key & Unique Key

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-trick . com/tutorial/sqlserver/v2bs260912 - difference—主键和惟一键- key.html找到详细信息

#8

• Think the table name is employe.
• 认为表名是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是主ID，而公民ID是惟一的。希望你理解。我们可以在表中使用多个唯一键。

#9

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.

一个表只能有一个主键。如果您想对两个或多个列强制惟一性，那么我们使用唯一键约束。

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 keys do not allow null values, whereas unique keys allow one null value

2。主键不允许空值，而唯一键允许一个空值。

Alter Table Table_Name
Add Constraint Constraint_Name Unique/Primary Key(Column_Name)

#10

If your Database design is such that their is no need of foreign key, then you can go with Unique key( but remember unique key allow single null value ).

如果您的数据库设计不需要外键，那么您可以使用唯一键(但请记住唯一键允许单个空值)。

If you database demand foreign key then you leave with no choice you have to go with primary key.

如果数据库需要外键，则必须使用主键。

To see the difference between unique and primary key visit here

在这里查看惟一键和主键访问之间的区别

#11

Simply Primary Key is a unique and can't be null, unique can be null and may not be unique.

简单的主键是唯一的，不能为空，唯一可以为空，也可能不是唯一的。

#12

1. See Screenshots for reference

   看到截图供参考

   Using Primary Key data is stored in database by default in ascending order clustered index**

   使用主键数据默认存储在数据库中，以升序聚集索引**

   In Sql Server See Screen Shot

   在Sql Server中看到屏幕截图

   By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index, whereas by default, Unique key is a unique non-clustered index.**

   默认情况下，主键是聚集索引，数据库表中的数据物理地按照聚集索引的顺序组织，而默认情况下，唯一键是唯一的非聚集索引。**

2. We can have only one Primary key in a table, whereas we can have more than one unique key in a table.

   表中只能有一个主键，而表中可以有多个唯一键。

3. Primary Key can't accept null values, whereas unique key can accept only one null value.

   主键不能接受空值，而唯一键只能接受一个空值。

4. Primary Key is Always Unique & Never Null, Whereas Unique key can accept only one null value.

   主键始终是唯一的&从不为空，而唯一的键只能接受一个空值。