When should I use KEY, PRIMARY KEY, UNIQUE KEY and INDEX?
什么时候使用键、主键、唯一键和索引?
9 个解决方案
#1
260
KEY and INDEX are synonyms in MySQL. They mean the same thing. In databases you would use indexes to improve the speed of data retrieval. An index is typically created on columns used in JOIN, WHERE, and ORDER BY clauses.
KEY和INDEX是MySQL的同义词。它们的意思是一样的。在数据库中，可以使用索引来提高数据检索的速度。索引通常创建在连接、WHERE和ORDER BY子句中使用的列上。
Imagine you have a table called users and you want to search for all the users which have the last name 'Smith'. Without an index, the database would have to go through all the records of the table: this is slow, because the more records you have in your database, the more work it has to do to find the result. On the other hand, an index will help the database skip quickly to the relevant pages where the 'Smith' records are held. This is very similar to how we, humans, go through a phone book directory to find someone by the last name: We don't start searching through the directory from cover to cover, as long we inserted the information in some order that we can use to skip quickly to the 'S' pages.
假设您有一个名为users的表，您想要搜索所有具有姓“Smith”的用户。如果没有索引，数据库就必须遍历表的所有记录:这是很慢的，因为数据库中的记录越多，查找结果的工作量就越大。另一方面，索引将帮助数据库快速跳转到保存“史密斯”记录的相关页面。人类,这非常类似于我们如何经历一个电话簿目录找到别人的姓:我们不开始搜索目录从头至尾,只要我们插入一些订单的信息,我们可以使用它来快速跳过“S”页面。
Primary keys and unique keys are similar. A primary key is a column, or a combination of columns, that can uniquely identify a row. It is a special case of unique key. A table can have at most one primary key, but more than one unique key. When you specify a unique key on a column, no two distinct rows in a table can have the same value.
主键和唯一键是相似的。主键是一列或一组列，它们可以惟一地标识一行。它是唯一键的特殊情况。一个表最多可以有一个主键，但是有多个唯一键。当您在列上指定唯一的键时，表中没有两个不同的行具有相同的值。
Also note that columns defined as primary keys or unique keys are automatically indexed in MySQL.
还要注意，在MySQL中，定义为主键或唯一键的列是自动索引的。
#2
48
KEY and INDEX are synonyms.
键和索引是同义词。
You should add an index when performance measurements and EXPLAIN shows you that the query is inefficient because of a missing index. Adding an index can improve the performance of queries (but it can slow down modifications to the table).
当性能度量和EXPLAIN显示查询由于缺少索引而效率低下时，您应该添加一个索引。添加索引可以提高查询的性能(但是会减慢对表的修改)。
You should use UNIQUE when you want to contrain the values in that column (or columns) to be unique, so that attempts to insert duplicate values result in an error.
当您想要使列(或列)中的值为唯一时，应该使用UNIQUE，以便尝试插入重复的值会导致错误。
A PRIMARY KEY is both a unique constraint and it also implies that the column is NOT NULL. It is used to give an identity to each row. This can be useful for joining with another table via a foreign key constraint. While it is not required for a table to have a PRIMARY KEY it is usually a good idea.
主键既是唯一的约束，也意味着列不是空的。它用于给每一行提供一个标识。这对于通过外键约束连接另一个表非常有用。虽然表不需要主键，但这通常是个好主意。
#3
37
Primary key does not allow null value but unique key allows null value.
主键不允许空值，但惟一键允许空值。
We can declare only one primary key in a table but a table can have multiple unique key(column assign).
我们只能在表中声明一个主键，但是表可以有多个唯一键(列分配)。
#4
16
PRIMARY KEY AND UNIQUE KEY are similar except it has different functions. Primary key makes the table row unique (i.e, there cannot be 2 row with the exact same key). You can only have 1 primary key in a database table.
主键和唯一键是相似的，只是功能不同。主键使表行惟一(i)。e，不可能有两行键相同)。数据库表中只能有一个主键。
Unique key makes the table column in a table row unique (i.e., no 2 table row may have the same exact value). You can have more than 1 unique key table column (unlike primary key which means only 1 table column in the table is unique).
惟一键使表行中的表列惟一(例如。，第二个表行可能具有相同的值)。您可以有多个唯一键表列(与主键不同，主键意味着表中只有一个表列是唯一的)。
INDEX also creates uniqueness. MySQL (example) will create a indexing table for the column that is indexed. This way, it's easier to retrieve the table row value when the query is queried on that indexed table column. The disadvantage is that if you do many updating/deleting/create, MySQL has to manage the indexing tables (and that can be a performance bottleneck).
指数也创造独特性。MySQL(示例)将为被索引的列创建索引表。这样，当在索引表列上查询查询查询查询查询查询时，就更容易检索表行值。缺点是，如果您进行许多更新/删除/创建，MySQL必须管理索引表(这可能是性能瓶颈)。
Hope this helps.
希望这个有帮助。
#5
10
Unique Keys: The columns in which no two rows are similar
唯一键:没有两行相似的列
Primary Key: Collection of minimum number of columns which can uniquely identify every row in a table (i.e. no two rows are similar in all the columns constituting primary key). There can be more than one primary key in a table. If there exists a unique-key then it is primary key (not "the" primary key) in the table. If there does not exist a unique key then more than one column values will be required to identify a row like (first_name, last_name, father_name, mother_name) can in some tables constitute primary key.
主键:可以唯一标识表中每一行的最小列数的集合(即，组成主键的所有列中没有两行是相似的)。表中可以有多个主键。如果存在一个惟一的键，那么它就是表中的主键(而不是“主键”)。如果不存在唯一键，那么需要多个列值来标识一个行，比如(first_name、last_name、father_name、mother_name)，在某些表中可以构成主键。
Index: used to optimize the queries. If you are going to search or sort the results on basis of some column many times (eg. mostly people are going to search the students by name and not by their roll no.) then it can be optimized if the column values are all "indexed" for example with a binary tree algorithm.
索引:用于优化查询。如果你要根据某一列多次搜索或排序结果(如。大多数人会根据学生的名字而不是他们的名字来搜索)然后，如果列值都被“索引”(例如，使用二叉树算法)，就可以对其进行优化。
#6
10
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.
一个表只能有一个主键。如果您想在2个或多个列上执行唯一性，那么我们使用唯一的键约束。
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 key does not allow nulls, where as unique key allows one null
2。主键不允许为空，其中唯一键允许为空
#7
6
Primary key - we can put only one primary key on a table into a table and we can not left that column blank when we are entering the values into the table.
主键——我们只能将表上的一个主键放在表中，并且在将值输入表时不能将该列留空。
Unique Key - we can put more than one unique key on a table and we may left that column blank when we are entering the values into the table. column take unique values (not same) when we applied primary & unique key.
唯一的键——我们可以在一个表上放置多个唯一的键，当我们将值输入到表中时，我们可能会将该列保留为空。列在应用主键和唯一键时取唯一值(不相同)。
#8
4
The primary key is used to work with different tables. This is the foundation of relational databases. If you have a book database it's better to create 2 tables - 1) books and 2) authors with INT primary key "id". Then you use id in books instead of authors name.
主键用于处理不同的表。这是关系数据库的基础。如果您有一个图书数据库，最好创建两个表——1)图书和2)作者与INT主键“id”。然后在书中使用id而不是作者名。
The unique key is used if you don't want to have repeated entries. For example you may have title in your book table and want to be sure there is only one entry for each title.
如果不希望重复条目，则使用惟一键。例如，您可能在图书表中有标题，并希望确保每个标题只有一个条目。
#9
1
Unique Key :
独特的关键:
- More than one value can be null.
- 多个值可以为空。
- No two tuples can have same values in unique key.
- 在唯一键中，任何两个元组都不能有相同的值。
- One or more unique keys can be combined to form a primary key, but not vice versa.
- 一个或多个唯一的键可以组合成一个主键，反之亦然。
Primary Key
主键
- Can contain more than one unique keys.
- 可以包含多个唯一键。
- Uniquely represents a tuple.
- 独特的代表一个元组。
#1
260
KEY and INDEX are synonyms in MySQL. They mean the same thing. In databases you would use indexes to improve the speed of data retrieval. An index is typically created on columns used in JOIN, WHERE, and ORDER BY clauses.
KEY和INDEX是MySQL的同义词。它们的意思是一样的。在数据库中，可以使用索引来提高数据检索的速度。索引通常创建在连接、WHERE和ORDER BY子句中使用的列上。
Imagine you have a table called users and you want to search for all the users which have the last name 'Smith'. Without an index, the database would have to go through all the records of the table: this is slow, because the more records you have in your database, the more work it has to do to find the result. On the other hand, an index will help the database skip quickly to the relevant pages where the 'Smith' records are held. This is very similar to how we, humans, go through a phone book directory to find someone by the last name: We don't start searching through the directory from cover to cover, as long we inserted the information in some order that we can use to skip quickly to the 'S' pages.
假设您有一个名为users的表，您想要搜索所有具有姓“Smith”的用户。如果没有索引，数据库就必须遍历表的所有记录:这是很慢的，因为数据库中的记录越多，查找结果的工作量就越大。另一方面，索引将帮助数据库快速跳转到保存“史密斯”记录的相关页面。人类,这非常类似于我们如何经历一个电话簿目录找到别人的姓:我们不开始搜索目录从头至尾,只要我们插入一些订单的信息,我们可以使用它来快速跳过“S”页面。
Primary keys and unique keys are similar. A primary key is a column, or a combination of columns, that can uniquely identify a row. It is a special case of unique key. A table can have at most one primary key, but more than one unique key. When you specify a unique key on a column, no two distinct rows in a table can have the same value.
主键和唯一键是相似的。主键是一列或一组列，它们可以惟一地标识一行。它是唯一键的特殊情况。一个表最多可以有一个主键，但是有多个唯一键。当您在列上指定唯一的键时，表中没有两个不同的行具有相同的值。
Also note that columns defined as primary keys or unique keys are automatically indexed in MySQL.
还要注意，在MySQL中，定义为主键或唯一键的列是自动索引的。
#2
48
KEY and INDEX are synonyms.
键和索引是同义词。
You should add an index when performance measurements and EXPLAIN shows you that the query is inefficient because of a missing index. Adding an index can improve the performance of queries (but it can slow down modifications to the table).
当性能度量和EXPLAIN显示查询由于缺少索引而效率低下时，您应该添加一个索引。添加索引可以提高查询的性能(但是会减慢对表的修改)。
You should use UNIQUE when you want to contrain the values in that column (or columns) to be unique, so that attempts to insert duplicate values result in an error.
当您想要使列(或列)中的值为唯一时，应该使用UNIQUE，以便尝试插入重复的值会导致错误。
A PRIMARY KEY is both a unique constraint and it also implies that the column is NOT NULL. It is used to give an identity to each row. This can be useful for joining with another table via a foreign key constraint. While it is not required for a table to have a PRIMARY KEY it is usually a good idea.
主键既是唯一的约束，也意味着列不是空的。它用于给每一行提供一个标识。这对于通过外键约束连接另一个表非常有用。虽然表不需要主键，但这通常是个好主意。
#3
37
Primary key does not allow null value but unique key allows null value.
主键不允许空值，但惟一键允许空值。
We can declare only one primary key in a table but a table can have multiple unique key(column assign).
我们只能在表中声明一个主键，但是表可以有多个唯一键(列分配)。
#4
16
PRIMARY KEY AND UNIQUE KEY are similar except it has different functions. Primary key makes the table row unique (i.e, there cannot be 2 row with the exact same key). You can only have 1 primary key in a database table.
主键和唯一键是相似的，只是功能不同。主键使表行惟一(i)。e，不可能有两行键相同)。数据库表中只能有一个主键。
Unique key makes the table column in a table row unique (i.e., no 2 table row may have the same exact value). You can have more than 1 unique key table column (unlike primary key which means only 1 table column in the table is unique).
惟一键使表行中的表列惟一(例如。，第二个表行可能具有相同的值)。您可以有多个唯一键表列(与主键不同，主键意味着表中只有一个表列是唯一的)。
INDEX also creates uniqueness. MySQL (example) will create a indexing table for the column that is indexed. This way, it's easier to retrieve the table row value when the query is queried on that indexed table column. The disadvantage is that if you do many updating/deleting/create, MySQL has to manage the indexing tables (and that can be a performance bottleneck).
指数也创造独特性。MySQL(示例)将为被索引的列创建索引表。这样，当在索引表列上查询查询查询查询查询查询时，就更容易检索表行值。缺点是，如果您进行许多更新/删除/创建，MySQL必须管理索引表(这可能是性能瓶颈)。
Hope this helps.
希望这个有帮助。
#5
10
Unique Keys: The columns in which no two rows are similar
唯一键:没有两行相似的列
Primary Key: Collection of minimum number of columns which can uniquely identify every row in a table (i.e. no two rows are similar in all the columns constituting primary key). There can be more than one primary key in a table. If there exists a unique-key then it is primary key (not "the" primary key) in the table. If there does not exist a unique key then more than one column values will be required to identify a row like (first_name, last_name, father_name, mother_name) can in some tables constitute primary key.
主键:可以唯一标识表中每一行的最小列数的集合(即，组成主键的所有列中没有两行是相似的)。表中可以有多个主键。如果存在一个惟一的键，那么它就是表中的主键(而不是“主键”)。如果不存在唯一键，那么需要多个列值来标识一个行，比如(first_name、last_name、father_name、mother_name)，在某些表中可以构成主键。
Index: used to optimize the queries. If you are going to search or sort the results on basis of some column many times (eg. mostly people are going to search the students by name and not by their roll no.) then it can be optimized if the column values are all "indexed" for example with a binary tree algorithm.
索引:用于优化查询。如果你要根据某一列多次搜索或排序结果(如。大多数人会根据学生的名字而不是他们的名字来搜索)然后，如果列值都被“索引”(例如，使用二叉树算法)，就可以对其进行优化。
#6
10
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.
一个表只能有一个主键。如果您想在2个或多个列上执行唯一性，那么我们使用唯一的键约束。
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 key does not allow nulls, where as unique key allows one null
2。主键不允许为空，其中唯一键允许为空
#7
6
Primary key - we can put only one primary key on a table into a table and we can not left that column blank when we are entering the values into the table.
主键——我们只能将表上的一个主键放在表中，并且在将值输入表时不能将该列留空。
Unique Key - we can put more than one unique key on a table and we may left that column blank when we are entering the values into the table. column take unique values (not same) when we applied primary & unique key.
唯一的键——我们可以在一个表上放置多个唯一的键，当我们将值输入到表中时，我们可能会将该列保留为空。列在应用主键和唯一键时取唯一值(不相同)。
#8
4
The primary key is used to work with different tables. This is the foundation of relational databases. If you have a book database it's better to create 2 tables - 1) books and 2) authors with INT primary key "id". Then you use id in books instead of authors name.
主键用于处理不同的表。这是关系数据库的基础。如果您有一个图书数据库，最好创建两个表——1)图书和2)作者与INT主键“id”。然后在书中使用id而不是作者名。
The unique key is used if you don't want to have repeated entries. For example you may have title in your book table and want to be sure there is only one entry for each title.
如果不希望重复条目，则使用惟一键。例如，您可能在图书表中有标题，并希望确保每个标题只有一个条目。
#9
1
Unique Key :
独特的关键:
- More than one value can be null.
- 多个值可以为空。
- No two tuples can have same values in unique key.
- 在唯一键中，任何两个元组都不能有相同的值。
- One or more unique keys can be combined to form a primary key, but not vice versa.
- 一个或多个唯一的键可以组合成一个主键，反之亦然。
Primary Key
主键
- Can contain more than one unique keys.
- 可以包含多个唯一键。
- Uniquely represents a tuple.
- 独特的代表一个元组。