database 主键和索引键有什么区别

What is the difference between a primary key and a index key

提问by pavan

Can anyone tell me what is the difference between a primary key and index key. And when to use which?

谁能告诉我主键和索引键有什么区别。什么时候使用哪个？

回答by paxdiablo

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.

这两个索引（姓氏和部门）都不允许 NULL（可能）并允许重复（几乎可以肯定），并且它们对于加快查找具有（例如）姓氏 'Corleone' 或工作的任何人的查询很有用“杀手”部门。

回答by nvogel

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.

短语“索引键”混合了这两个完全不同的词，如果您想避免混淆，最好避免使用。“索引键”有时用于表示“索引中的属性集”。然而，有问题的属性集不一定是键，因为它们可能不是唯一的。

回答by Kanagavelu Sugumar

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 数据库通过在唯一键或主键上创建唯一索引来对表强制执行 UNIQUE 键或 PRIMARY KEY 完整性约束。启用约束时，该索引由数据库自动创建。

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 INDEXstatement 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:

如果您需要对与UNIQUE and PRIMARY KEY 约束关联的索引进行更明确的控制，数据库允许您：

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 INDEXclause.

这些选项是使用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

回答by Will Cross

Other responses are defining the Primary Key, but not the Primary Index.

其他响应定义了主键，而不是主索引。

A Primary Index isn't an index on the Primary Key.

主索引不是主键上的索引。

A Primary Index is your table's data structure, but only if your data structure is orderedby the Primary Key, thus allowing efficient lookups without a requiring a separate data structure to look up records by the Primary Key.

主索引是您的表的数据结构，但前提是您的数据结构按主键排序，从而允许高效查找，而无需单独的数据结构按主键查找记录。

All databases (that I'm aware of) have a Primary Key.

所有数据库（我知道的）都有一个主键。

Not all databases have a Primary Index. Most of those that don't build a secondary index on the Primary Key by default.

并非所有数据库都有主索引。大多数默认情况下不在主键上构建二级索引。