The relationship between MySQL primary key and index and the difference analysis _mysql

A relational database relies on a primary key, which is the cornerstone of the physical schema of the database. Primary keys are only available on a physical level for two purposes:

Uniquely identifies a row.
As an object that can be referenced effectively by a foreign key.
An index is a special file (an index on a INNODB datasheet is an integral part of a tablespace) that contains a reference pointer to all records in the datasheet. The following are some of the differences and connections between primary keys and indexes.

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 attributes that uniquely identifies a row in a table, one table can have only one primary key, but can have multiple candidate indexes. Because a primary key can uniquely identify a row of records, you can ensure that no confusedly errors occur when you perform data updates and deletions. primary keys, in addition to the above, often form referential integrity constraints with foreign keys to prevent data inconsistency. The primary key plays a very important role in the design of the database.

The primary key guarantees that the unique and primary key fields of the record are 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. A table can have multiple uniqueness indexes, but only one primary key.

3. The primary key column does not allow Nulls, while 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, the meaning is that the primary key does not exist, and the index actually exists in the database, the primary key is generally built, mainly to avoid a table with the same record, the index can not be built, but if you need to query the table operation, it is best to build, This will speed up the retrieval.

FAQ

1. What is a primary key?

I come to specific things to say, the book we have seen it, did not read the search for the book, look at it every page is not 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 is equivalent to the directory of books, with a catalog we can quickly know the basic content and structure of the book, the same data index, it can speed up the query speed of the datasheet.

3. Primary key primary index analogy, and their role?

The primary key is to identify the uniqueness of the database record, does not allow duplicate records, and the key value cannot be null, and the primary key is also a special index.
Only one primary key is allowed in the datasheet, but can have multiple indexes.
Using the primary key database automatically creates the primary index, or you can create an index on a non primary key to facilitate query efficiency.
An index can increase the speed of a query, which is equivalent to a dictionary directory where you can quickly query for desired results without requiring a full table scan.
The value of an index outside the primary key index can be null.
A primary key can also consist of multiple fields that make up a composite primary key, while the primary key is definitely a unique index.
A unique index indicates that the index value is unique, can consist of one or several fields, and a table can have multiple unique indexes.