主键和索引键有什么区别

发布于 2024-10-24 10:05:36 字数 38 浏览 2 评论 0原文

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

我什么时候使用每一个?

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

When do I use each?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

请叫√我孤独 2024-10-31 10:05:36

主键是一种特殊的索引,因为:

  • 只能有一个;
  • 它不能为空;而且
  • 它必须是独一无二的。

您倾向于使用主键作为行最自然的唯一标识符(例如社会保障号、员工 ID 等,尽管有一种观点认为您应该始终为此使用人工代理键​​)。

另一方面,索引可用于基于其他列的快速检索。例如,员工数据库可能以您的员工编号作为主键,但也可能以您的姓氏或部门为索引。

这两个索引(姓氏和部门)都不允许 NULL(可能)并允许重复(几乎肯定),并且它们对于加快查找姓氏为“Corleone”或在其中工作的任何人的查询非常有用。 “杀手”部门。

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

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.

掀纱窥君容 2024-10-31 10:05:36

键(最小超键)是一组属性,其值对于每个元组(表中某个时间点的每一行)都是唯一的。

索引是一种性能优化功能,可以更快地访问数据。

键通常是索引的良好候选者,并且某些 DBMS 会自动为键创建索引,但事实并非如此。

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

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.

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.

白衬杉格子梦 2024-10-31 10:05:36

Oracle 数据库通过在唯一键或主键上创建唯一索引来对表强制执行 UNIQUE 键或 PRIMARY KEY 完整性约束。当启用约束时,该索引由数据库自动创建。

您可以使用 SQL 语句 CREATE INDEX 显式创建索引(在完整性约束之外)。

索引可以是唯一的,也可以是非唯一的。唯一索引保证表中没有两行在键列(或列)中具有重复值。非唯一索引不会对列值施加此限制。

使用CREATE UNIQUE INDEX语句创建唯一索引。

指定与约束关联的索引

如果您需要对与 UNIQUE 和 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

使用 USING INDEX 指定这些选项> 条款。

示例:

 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

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.

You can create indexes explicitly (outside of integrity constraints) using the SQL statement 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.

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.

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

我很OK 2024-10-31 10:05:36

其他响应定义了主键,但没有定义主索引。

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

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

所有数据库(据我所知)都有一个主键。

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

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 ordered by 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.

玩物 2024-10-31 10:05:36

如果主键以外的其他列将或可以用于访问您的数据,请创建索引。

create an index if some other columns other than your primary key will be or could be used to access your data.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文