Connection and difference analysis between MySQL primary key and index _ Mysql

This article mainly introduces the connection and difference between MySQL primary key and index. if you are using mysql, you can read this article to introduce the connection and difference between MySQL primary key and index. if you are using mysql, you can read it.

Relational databases depend on primary keys, which are the foundation of the physical mode of databases. The primary key has only two purposes on the physical layer:

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

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

A primary key uniquely identifies a row in a table. a table can have only one primary key, but multiple candidate indexes. Because the primary key can uniquely identify a row of records, it can ensure that no error is reported during data update and deletion. In addition to the above functions, the primary key often forms a reference integrity constraint with the foreign key to prevent data inconsistency. During database design, primary keys play an important role.

The primary key can ensure that the record is unique and the primary key field is not empty. 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 unique indexes, but only one primary key.

3. null values are not allowed for primary key columns, while null values are allowed for unique index columns.

4. indexes can increase the query speed.

In fact, both the primary key and the index are keys, but the primary key is the logical key and the index is the physical key, which means that the primary key does not actually exist. the index actually exists in the database, and the primary key is usually created, it is mainly used to prevent a table from having the same record, and the index can not be created. However, if you need to query the table, it is best to create an index to accelerate the retrieval speed.

FAQ

1. what is a primary key?

Let me talk about specific things. you have read all the books. if you haven't read the books, please refer to them and see if each page has a page number, the primary key of the data table is equivalent to the page number.

2. what is the index?

We also take the book as an example. The index is equivalent to the book Directory. with the Directory, we can quickly know the basic content and structure of the book. The data index is also the same, which can speed up data table query.

3. primary key primary index analogy and their functions?

The primary key is used to identify the uniqueness of database records. duplicate records are not allowed, and the key value cannot be blank. the primary key is also a special index.
Only one primary key is allowed in the data table, but multiple indexes are allowed.
When the primary key is used, the 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 query speed, which is equivalent to a dictionary Directory. you can use it to quickly query the desired results without having to perform a full table scan.
The value of the index outside the primary key index can be null.
A primary key can also be composed of multiple fields to form a composite primary key, and the primary key must be a unique index.
A unique index indicates that the index value is unique and can contain one or more fields. a table can have multiple unique indexes.