The connection and difference between mysql primary keys and indexes

Relational databases rely on primary keys, which are the cornerstone of the database's physical model. The primary key has only two purposes on the physical level:

To uniquely identify a row.

As an object that can be validly referenced by foreign keys.

Indexes are special files (the indexes on the InnoDB data table are a component of the table space). They contain reference pointers to all records in the data table. The following are some differences and connections between primary keys and indexes.

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

The so-called primary key is an attribute or attribute group that can uniquely identify a row in the table. A table can only have one primary key, but it can have multiple candidate indexes. Because the primary key can uniquely identify a row of records, it can ensure that no errors will occur when updating and deleting data. In addition to the above functions, primary keys often form referential integrity constraints with foreign keys to prevent data inconsistency. When designing a database, primary keys play a very 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. There can be multiple unique indexes in a table, but there can only be one primary key.

3. Primary key columns do not allow null values, while unique index columns allow null values.

4. Indexes can improve query speed.

In fact, the primary key and the index are both keys, but the primary key is a logical key and the index is a physical key, which means that the primary key does not actually exist, but the index actually exists in the database. The primary key generally needs to be created, mainly to avoid having Generally, the index does not need to be built for the same records, but if you need to query the table, it is best to build it, which can speed up the retrieval.

FAQ

1. What is the primary key?

Let me talk about the specific things. Everyone has read the book. If you haven’t read it, find a copy and look at it to see if there is a page number on each page. Let’s The primary key of the data table is equivalent to this page number, you understand.

2. So what is an index?

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

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

The primary key is to identify the uniqueness of the database record. Duplication of records is not allowed, and the key value cannot be empty. The primary key is also a special index.

Only one primary key is allowed in the data table, but there can be multiple indexes.

If you use the primary key, the database will automatically create a primary index. You can also create an index on a non-primary key to facilitate query efficiency.

The index can improve the query speed. It is equivalent to the directory of the dictionary. You can quickly query the desired results through it without performing a full table scan.

The value of the primary key index and foreign index can be empty.

The primary key can also be composed of multiple fields to form a composite primary key. At the same time, the primary key must also be the only index.

Unique index means that the index value is unique and can be composed of one or several fields. A table can have multiple unique indexes.