主键，唯一键和外键约束以及索引有什么区别？ [英] What is the difference between primary, unique and foreign key constraints, and indexes?

问题描述

主键，唯一键和外键约束以及索引有什么区别？

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

解决方案

Key和Unique Key是实体完整性约束

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

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

• KEY或INDEX是指正常的非唯一索引。允许索引的不同值，因此索引可以包含索引的所有列中具有相同值的行。这些索引不会对数据强制执行任何结构，因此它们只用于加快查询速度。


• UNIQUE指的是索引的所有行必须唯一的索引。也就是说，对于该索引中的所有列，同一行可能不具有与另一行相同的非NULL值。除了用于加速查询之外，UNIQUE索引还可用于对数据执行结构，因为数据库系统不允许在插入或更新数据时破坏此不同的值规则。您的数据库系统可能允许在允许NULL值的列上使用UNIQUE索引，在这种情况下，如果两个行都包含NULL值（NULL被认为不等于其自身），则允许两个行相同，但这可能不受欢迎，具体取决于您应用程序。


• PRIMARY的行为与UNIQUE索引完全相同，除了它始终命名为PRIMARY，并且表上可能只有一个（并且应该总是有一个;数据库系统不强制这样做）。 PRIMARY索引旨在作为唯一标识表中任何行的方法，因此不应在允许NULL值的任何列上使用。您的PRIMARY索引应始终位于足以唯一标识行的最小列数。通常，这只是一个包含唯一自动递增数字的列，但如果有其他任何可以唯一标识行的地方，例如国家/地区列表中的countrycode，则可以改用。


• FULLTEXT索引不同于上述所有，它们的行为在数据库系统之间有更大的不同。与上面三个不同的是，FULLTEXT索引通常是b-tree（允许从最左列开始选择，排序或范围）或散列（允许从最左列开始选择），FULLTEXT索引仅对于使用MATCH（）/ AGAINST（）子句。

请参阅

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

I work on Oracle 10g and SQL Server 2008

解决方案

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

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

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

这篇关于主键，唯一键和外键约束以及索引有什么区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！