Can anyone tell me what is the difference between a primary key and index key. And when to use which?
有人能告诉我主键和索引键的区别吗?什么时候用哪个?
3 个解决方案
#1
36
A primary key is a special kind of index in that:
主键是一种特殊的索引:
- there can be only one;
- 只有一个;
- it cannot be nullable; and
- 它不能可以为空;和
- it must be unique.
- 它必须是唯一的。
You tend to use the primary key as the most natural unique identifier for a row (such as social security number, employee ID and so forth, although there is a school of thought that you should always use an artificial surrogate key for this).
您倾向于使用主键作为一行最自然的惟一标识符(例如社会保险号、员工ID等，尽管有一种观点认为您应该始终为此使用人工代理键)。
Indexes, on the other hand, can be used for fast retrieval based on other columns. For example, an employee database may have your employee number as the primary key but it may also have an index on your last name or your department.
另一方面，索引可以用于基于其他列的快速检索。例如，一个员工数据库可能以您的员工编号作为主键，但它也可能包含您的姓或部门的索引。
Both of these indexes (last name and department) would disallow NULLs (probably) and allow duplicates (almost certainly), and they would be useful to speed up queries looking for anyone with (for example) the last name 'Corleone' or working in the 'HitMan' department.
这两个索引(姓和部门)都不允许使用NULLs(可能)，允许重复(几乎肯定)，并且它们对于加快查询速度(例如)查找姓氏为“Corleone”的人或在“HitMan”部门工作的人非常有用。
#2
14
A key (minimal superkey) is a set of attributes, the values of which are unique for every tuple (every row in the table at some point in time).
键(最小超键)是一组属性，其值对于每个元组(表中某一时刻的每一行)都是唯一的。
An index is a performance optimisation feature that enables data to be accessed faster.
索引是一种性能优化特性，可以使数据更快地被访问。
Keys are frequently good candidates for indexing and some DBMSs automatically create indexes for keys, but that doesn't have to be so.
键通常是索引的很好的候选者，一些dbms会自动为键创建索引，但这并不是必须的。
The phrase "index key" mixes these two quite different words and might be best avoided if you want to avoid any confusion. "Index key" is sometimes used to mean "the set of attributes in an index". However the set of attributes in question are not necessarily a key because they may not be unique.
“索引键”这个短语混合了这两个完全不同的词，如果你想避免混淆，最好避免使用。“索引键”有时用于表示“索引中的属性集”。但是，所讨论的属性集合不一定是键，因为它们可能不是惟一的。
#3
3
Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by the database when the constraint is enabled.
Oracle数据库通过在惟一键或主键上创建惟一索引来对表执行惟一键或主键完整性约束。当启用约束时，数据库将自动创建该索引。
You can create indexes explicitly (outside of integrity constraints) using the SQL statement CREATE INDEX .
可以使用SQL语句create INDEX显式地(在完整性约束之外)创建索引。
Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values.
索引可以是惟一的，也可以是非惟一的。唯一索引保证表的两行在键列(或列)中没有重复的值。非唯一索引不会对列值施加此限制。
Use the CREATE UNIQUE INDEX statement to create a unique index.
使用CREATE UNIQUE INDEX语句创建一个惟一的索引。
Specifying the Index Associated with a Constraint
If you require more explicit control over the indexes associated with UNIQUE and PRIMARY KEY constraints, the database lets you:
如果您需要对与唯一和主键约束相关的索引进行更明确的控制，那么数据库允许您:
1. Specify an existing index that the database is to use
to enforce the constraint
2. Specify a CREATE INDEX statement that the database is to use to create
the index and enforce the constraint
These options are specified using the USING INDEX clause.
使用using INDEX子句指定这些选项。
Example:
例子:
CREATE TABLE a (
a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));
http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm
#1
36
A primary key is a special kind of index in that:
主键是一种特殊的索引:
- there can be only one;
- 只有一个;
- it cannot be nullable; and
- 它不能可以为空;和
- it must be unique.
- 它必须是唯一的。
You tend to use the primary key as the most natural unique identifier for a row (such as social security number, employee ID and so forth, although there is a school of thought that you should always use an artificial surrogate key for this).
您倾向于使用主键作为一行最自然的惟一标识符(例如社会保险号、员工ID等，尽管有一种观点认为您应该始终为此使用人工代理键)。
Indexes, on the other hand, can be used for fast retrieval based on other columns. For example, an employee database may have your employee number as the primary key but it may also have an index on your last name or your department.
另一方面，索引可以用于基于其他列的快速检索。例如，一个员工数据库可能以您的员工编号作为主键，但它也可能包含您的姓或部门的索引。
Both of these indexes (last name and department) would disallow NULLs (probably) and allow duplicates (almost certainly), and they would be useful to speed up queries looking for anyone with (for example) the last name 'Corleone' or working in the 'HitMan' department.
这两个索引(姓和部门)都不允许使用NULLs(可能)，允许重复(几乎肯定)，并且它们对于加快查询速度(例如)查找姓氏为“Corleone”的人或在“HitMan”部门工作的人非常有用。
#2
14
A key (minimal superkey) is a set of attributes, the values of which are unique for every tuple (every row in the table at some point in time).
键(最小超键)是一组属性，其值对于每个元组(表中某一时刻的每一行)都是唯一的。
An index is a performance optimisation feature that enables data to be accessed faster.
索引是一种性能优化特性，可以使数据更快地被访问。
Keys are frequently good candidates for indexing and some DBMSs automatically create indexes for keys, but that doesn't have to be so.
键通常是索引的很好的候选者，一些dbms会自动为键创建索引，但这并不是必须的。
The phrase "index key" mixes these two quite different words and might be best avoided if you want to avoid any confusion. "Index key" is sometimes used to mean "the set of attributes in an index". However the set of attributes in question are not necessarily a key because they may not be unique.
“索引键”这个短语混合了这两个完全不同的词，如果你想避免混淆，最好避免使用。“索引键”有时用于表示“索引中的属性集”。但是，所讨论的属性集合不一定是键，因为它们可能不是惟一的。
#3
3
Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by the database when the constraint is enabled.
Oracle数据库通过在惟一键或主键上创建惟一索引来对表执行惟一键或主键完整性约束。当启用约束时，数据库将自动创建该索引。
You can create indexes explicitly (outside of integrity constraints) using the SQL statement CREATE INDEX .
可以使用SQL语句create INDEX显式地(在完整性约束之外)创建索引。
Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values.
索引可以是惟一的，也可以是非惟一的。唯一索引保证表的两行在键列(或列)中没有重复的值。非唯一索引不会对列值施加此限制。
Use the CREATE UNIQUE INDEX statement to create a unique index.
使用CREATE UNIQUE INDEX语句创建一个惟一的索引。
Specifying the Index Associated with a Constraint
If you require more explicit control over the indexes associated with UNIQUE and PRIMARY KEY constraints, the database lets you:
如果您需要对与唯一和主键约束相关的索引进行更明确的控制，那么数据库允许您:
1. Specify an existing index that the database is to use
to enforce the constraint
2. Specify a CREATE INDEX statement that the database is to use to create
the index and enforce the constraint
These options are specified using the USING INDEX clause.
使用using INDEX子句指定这些选项。
Example:
例子:
CREATE TABLE a (
a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));
http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm