主键约束、唯一键约束和外键约束以及索引之间的区别是什么?

What is the difference between primary, unique and foreign key constraints, and indexes?

主键约束、唯一键约束和外键约束以及索引之间的区别是什么?

I work on Oracle 10g and SQL Server 2008

我在Oracle 10g和SQL Server 2008上工作

7 个解决方案

#1

Primary Key and Unique Key are Entity integrity constraints

主键和惟一键是实体完整性约束。

Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist and no null values are entered.

主键允许惟一地标识表中的每一行，并确保不存在重复的行，不输入空值。

Unique key constraint is used to prevent the duplication of key values within the rows of a table and allow null values. (In oracle one null is not equal to another null).

唯一的关键约束是用来防止在表的行中重复键值，并允许空值。(在oracle中，一个null不等于另一个null)。

• KEY or INDEX refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain rows with identical values in all columns of the index. These indexes don't enforce any structure on your data so they are used only for speeding up queries.
• 键或索引指的是正常的非唯一索引。允许索引的非惟一值，因此索引可以在索引的所有列中包含具有相同值的行。这些索引不强制对数据执行任何结构，因此只用于加速查询。
• UNIQUE refers to an index where all rows of the index must be unique. That is, the same row may not have identical non-NULL values for all columns in this index as another row. As well as being used to speed up queries, UNIQUE indexes can be used to enforce structure on data, because the database system does not allow this distinct values rule to be broken when inserting or updating data. Your database system may allow a UNIQUE index on columns which allow NULL values, in which case two rows are allowed to be identical if they both contain a NULL value (NULL is considered not equal to itself), though this is probably undesirable depending on your application.
• UNIQUE指的是一个索引，其中索引的所有行都必须是唯一的。也就是说，对于该索引中的所有列，同一行可能不具有与另一行相同的非空值。除了用于加速查询之外，还可以使用惟一索引来加强数据结构，因为数据库系统不允许在插入或更新数据时破坏这种不同的值规则。您的数据库系统可能允许在允许空值的列上有一个惟一的索引，在这种情况下，如果两个行包含一个空值(NULL被认为不等于它自己)，那么允许两个行是相同的，尽管这可能是不受欢迎的，这取决于您的应用程序。
• PRIMARY acts exactly like a UNIQUE index, except that it is always named 'PRIMARY', and there may be only one on a table (and there should always be one; though some database systems don't enforce this). A PRIMARY index is intended as a way to uniquely identify any row in the table, so it shouldn't be used on any columns which allow NULL values. Your PRIMARY index should always be on the smallest number of columns that are sufficient to uniquely identify a row. Often, this is just one column containing a unique auto-incremented number, but if there is anything else that can uniquely identify a row, such as "countrycode" in a list of countries, you can use that instead.
• 主要的行为就像一个唯一的索引，除了它总是被命名为“PRIMARY”，而且可能只有一个在表上(并且应该始终有一个);虽然有些数据库系统并不强制执行这一点)。主索引用于惟一地标识表中的任何行，因此不应该在任何允许空值的列上使用主索引。主索引应该始终位于足够惟一地标识一行的最小列数上。通常，这只是一个列，其中包含一个惟一的自动递增的数字，但是如果有任何其他东西可以惟一地标识一行，例如国家列表中的“countrycode”，您可以使用它。
• FULLTEXT indexes are different to all of the above, and their behaviour differs more between database systems. Unlike the above three, which are typically b-tree (allowing for selecting, sorting or ranges starting from left most column) or hash (allowing for selection starting from left most column), FULLTEXT indexes are only useful for full text searches done with the MATCH() / AGAINST() clause.
• 全文索引与上述所有索引不同，它们的行为在数据库系统之间的差异更大。与上述三种情况不同的是，它是典型的b-树(允许从左大多数列开始选择、排序或范围)或散列(允许从左大部分列开始选择)，全文索引只适用于匹配()/ AGAINST()子句的全文搜索。

see Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?

看到MySQL中索引、主、惟一和全文之间的差异了吗?

#2

Primary Key: identify uniquely every row it can not be null. it can not be a duplicate.

主键:唯一地标识不为空的每一行。它不可能是复制品。

Foreign Key: create relationship between two tables. can be null. can be a duplicate  

外键:在两个表之间创建关系。可以为空。可以重复

#3

Here are some reference for you:

以下是对你的一些参考:

Primary & foreign key Constraint.

主键和外键约束。

Primary Key: A primary key is a field or combination of fields that uniquely identify a record in a table, so that an individual record can be located without confusion.

主键:主键是字段或字段的组合，这些字段惟一地标识表中的记录，因此可以不混淆地定位单个记录。

Foreign Key: A foreign key (sometimes called a referencing key) is a key used to link two tables together. Typically you take the primary key field from one table and insert it into the other table where it becomes a foreign key (it remains a primary key in the original table).

外键:外键(有时称为引用键)是连接两个表的键。通常，您从一个表中获取主键字段，并将其插入到另一个表中，在这个表中它将成为外键(它仍然是原始表中的主键)。

Index, on the other hand, is an attribute that you can apply on some columns so that the data retrieval done on those columns can be speed up.

另一方面，索引是可以应用于某些列的属性，以便可以加快对这些列进行的数据检索。

#4

Key/index : A key is an aspect of a LOGICAL database design, an index is an aspect of a PHYSICAL database design. A key corresponds to an integrity constraint, an index is a technique of physically recording values that can be usefully applied when enforcing those constraints.

键/索引:键是逻辑数据库设计的一个方面，索引是物理数据库设计的一个方面。键对应于完整性约束，索引是一种物理记录值的技术，在执行这些约束时可以有效地应用这些值。

Primary/foreign : A "primary" key is a set of attributes whose values must form a combination that is unique in the entire table. There can be more than one such set (> 1 key), and the word "primary" is a remnant from the earlier days when the designer was then forced to choose one of those multiple keys as being "the most important/relevant one". The reason for this was primarily in combination with foreign keys :

主/外:“主”键是一组属性，其值必须形成整个表中唯一的组合。可以有不止一个这样的集合(> 1键)，“primary”这个词是早期的一个遗留，那时设计者*选择其中的一个多键作为“最重要的/相关的”。原因主要是与外键结合:

Like a "primary" key, a "foreign" key is also a set of attributes. The values of these attributes must form a combination that is an existing primary key value in the referenced table. I don't know exactly how strict this rule still applies in SQL today. The terminology has remained anyway.

与“主”键一样，“外”键也是一组属性。这些属性的值必须形成一个组合，该组合是引用表中现有的主键值。我不知道这个规则在今天的SQL中到底有多严格。术语仍然存在。

Unique : keyword used to indicate that an index cannot accept duplicate entries. Unique indexes are obviously an excellent means to enforce primary keys. To the extent that the word 'unique' is used in contexts of LOGICAL design, it is superfluous, sloppy, unnecessary and confusing. Keys (primary keys, that is) are unique by definition.

Unique:用于指示索引不能接受重复条目的关键字。惟一索引显然是执行主键的最佳方法。在逻辑设计的语境中使用“unique”这个词是多余的、草率的、不必要的和混乱的。键(主键，也就是)是唯一的定义。

#5

1)A primary key is a set of one or more attributes that uniquely identifies tuple within relation.

主键是一个或多个属性的集合，它们惟一地标识关系中的元组。

2)A foreign key is a set of attributes from a relation scheme which can be uniquely identify tuples fron another relation scheme.

外键是关系方案中的一组属性，可以惟一地识别另一个关系方案中的元组。

#6

1. A primary key is a column or a set of columns that uniquely identify a row in a table. A primary key should be short, stable and simple. A foreign key is a field (or collection of fields) in a table whose value is required to match the value of the primary key for a second table. Usually a foreign key is in a table that is different from the table whose primary key it is required to match. A table can have multiple foreign keys.
2. 主键是一列或一组列，它们惟一地标识表中的一行。主键应该短、稳定、简单。外键是表中的字段(或字段集合)，该表的值必须与第二个表的主键值相匹配。通常，外键位于与需要匹配主键的表不同的表中。一个表可以有多个外键。
3. The primary key cannot accept null values. Foreign key can accept multiple.
4. 主键不能接受空值。外键可以接受多个。
5. We can have only one primary key in a table. We can have more than one foreign key in a table.
6. 表中只能有一个主键。我们可以在一张桌子上放不止一把外键。
7. By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index. Foreign key do not automatically create an index, clustered or non-clustered. You can manually create an index on foreign key.
8. 默认情况下，主键是聚集索引，数据库表中的数据在物理上按照聚集索引的顺序组织。外键不自动创建一个索引，聚集或非聚集。您可以在外键上手动创建索引。

#7

Primary key mainly prevent duplication and shows the uniqueness of columns Foreign key mainly shows relationship on two tables

主键主要防止重复，显示列的唯一性外键主要显示两个表上的关系