What is the relationship between MySQL primary key and index and what is the primary key and index?

The relational database relies on the primary key, which is the cornerstone of the database physical pattern. There are only two uses of the primary key at the physical level:

• Uniquely identifies a row.
• As an object that can be effectively referenced by a foreign key.

An index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain reference pointers to all records in the datasheet. Here are some of the differences and linkages between the primary key and the index.

1. The primary key must be a unique index, and the uniqueness index is not necessarily the primary key.

A primary key is a property or group of properties that uniquely identifies a row in a table, and a table can have only one primary key, but there may be multiple candidate indexes. Because a primary key uniquely identifies a row of records, you can ensure that you do not have pigtailed errors when you perform data updates and deletions. The primary key, in addition to the above, often forms referential integrity constraints with foreign keys to prevent inconsistent data. Database at design time, the primary key plays a very important role.

The primary key guarantees that the record is unique and the primary key domain is not empty, and the database management system automatically generates a unique index for the primary key, so the primary key is also a special index.

2. There can be multiple unique indexes in a table, but only one primary key.

3. The primary key column does not allow null values, and the uniqueness index column allows null values.

4. Indexing can increase the speed of queries.

In fact, the primary key and index are keys, but the primary key is a logical key, the index is a physical key, meaning that the primary key does not actually exist, and the index actually exists in the database, the primary key is generally to be built, mainly to avoid a table has the same record, the index can not be built, but if you need to query the table This can speed up the retrieval.

FAQ

1. What is a primary key?

I come to the specific things to say, the book everyone has seen it, did not read to look for Ben to turn, to see if it has a page number, our data table primary key is equivalent to this page number, understand it.

2. So what is the index?

We also take the book, the index equivalent to the book directory, with a catalogue we can quickly know the basic content and structure of the book, data index is the same, it can speed up the query speed of the data table.

3. Primary key index analogies, and their role?

• Primary keys are designed to identify database record uniqueness, do not allow record duplication, and the key value cannot be empty, and the primary key is also a special index.
• Only one primary key is allowed in the data table, but there can be multiple indexes.
• Using the primary key will automatically create the primary index, or you can create an index on a non-primary key to facilitate query efficiency.
• An index can improve the query speed, which is equivalent to the dictionary directory, which can be quickly queried to the desired results, without the need for a full table scan.
• The value of the index outside the primary key index can be null.
• A primary key can also consist of multiple fields, composing a composite primary key, and the primary key must also be a unique index.
• A unique index indicates that the index value is unique and can consist of one or several fields, and a table can have multiple unique indexes.

Turn (http://www.nowamagic.net/librarys/veda/detail/1954)

What is the relationship between MySQL primary key and index and what is the primary key and index?