SQL - 唯一键、主键和外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29863388/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - Unique Key, Primary Key & Foreign Key
提问by Jaipal Reddy K
What are the differences between Unique Key, Primary Keyand Foreign Keywith respect to concept of SQL?
唯一键、主键和外键在概念上有SQL什么区别？
How they are different from each other?
它们之间有何不同？
回答by Jaipal Reddy K
A PRIMARY Key and UNIQUE Key constraints both are similar and it provide unique enforce uniqueness of the column on which they are defined.
PRIMARY Key 和 UNIQUE Key 约束都是相似的，它提供了定义它们的列的唯一强制唯一性。
Primary Key
首要的关键
- Primary key cannot have a NULL value.
- Each table can have only one primary key.
- By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.
- Primary key can be related with another table's as a Foreign Key.
- We can generated ID automatically with the help of Auto Increment field. Primary key supports Auto Increment value.
- 主键不能有 NULL 值。
- 每个表只能有一个主键。
- 默认情况下，主键是聚簇索引，数据库表中的数据物理上是按照聚簇索引的顺序组织的。
- 主键可以作为外键与另一个表相关联。
- 我们可以借助 Auto Increment 字段自动生成 ID。主键支持自动递增值。
Unique Key
唯一键
- Unique Constraint may have a NULL value.
- Each table can have more than one Unique Constraint.
- By default, Unique key is a unique non-clustered index.
- Unique Constraint can not be related with another table's as a Foreign Key.
- Unique Constraint doesn't supports Auto Increment value.
- 唯一约束可能具有 NULL 值。
- 每个表可以有多个唯一约束。
- 默认情况下，唯一键是唯一的非聚集索引。
- 唯一约束不能作为外键与另一个表相关。
- 唯一约束不支持自动增量值。
Foreign Key
外键
- Foreign key is a field in the table that is primary key in another table.
- Foreign key can accept multiple null value.
- Foreign key do not automatically create an index, clustered or non-clustered. You can manually create an index on foreign key.
- We can have more than one foreign key in a table.
- There are actual advantages to having a foreign key be supported with a clustered index, but you get only one per table. What's the advantage? If you are selecting the parent plus all child records, you want the child records next to each other. This is easy to accomplish using a clustered index.
- Having a null foreign key is usually a bad idea. In the example below, the record in [dbo].[child] is what would be referred to as an "orphan record". Think long and hard before doing this.
- 外键是表中的一个字段，它是另一个表中的主键。
- 外键可以接受多个空值。
- 外键不会自动创建索引，聚簇或非聚簇。您可以在外键上手动创建索引。
- 我们可以在一张表中拥有多个外键。
- 使用聚簇索引支持外键有实际的好处，但每个表只能得到一个。有什么好处？如果您选择父记录和所有子记录，您希望子记录彼此相邻。使用聚集索引很容易实现这一点。
- 拥有一个空外键通常是一个坏主意。在下面的示例中，[dbo].[child] 中的记录将被称为“孤立记录”。在做这件事之前，要深思熟虑。
回答by Dattatray Ghevade
Note: we use constraint for enforce data integrity
注意：我们使用约束来强制执行数据完整性
Primary Key
1)can't insert null value
2) one table have one primary key
主键
1) 不能插入空值
2) 一张表有一个主键
Unique key 1) insert null value one at time 2)one table have multiple unique key 3) you can refereed as candidate key also
唯一键 1) 插入空值 1 2) 一张表有多个唯一键 3) 你也可以作为候选键
foreign key 1) maintain the relationship between two table and also multiple Note: without any constraint you get data in multiple table but you can not get data peoperly
外键 1) 维护两个表和多个表之间的关系 注意：没有任何约束，您可以在多个表中获取数据，但无法单独获取数据
回答by Faqir Aamir
A note about Unique key
关于唯一键的说明
The parent table in a Primary Key-Foreign Key relation is normally called as Primary Key table but PK is not mandatory in a parent table. A unique key/constraint in parent table is sufficient. As PK is always unique, it is often used as foreign key in another table. see this SO post
主键-外键关系中的父表通常称为主键表，但父表中的 PK 不是必需的。父表中的唯一键/约束就足够了。由于 PK 始终是唯一的，因此通常用作另一个表中的外键。看到这个SO帖子
