Primary key and index relationship in MySQL

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

• to uniquely identify a row.

• As an object that can be validly referenced by a foreign key.

Index is a special file (the index on the InnoDB data table is an integral part of the table space). They contain all records in the data table. reference pointer. 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 certain 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 certain row of records, it can be ensured that no errors will occur when performing data update and delete. 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. The primary key column does not allow null values, while the unique index column allows 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 built, mainly for To avoid having the same records in one table, the index generally does not need to be built, 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. Check to see if each page has a page number. The primary key of our data table is equivalent to this page number. You understand.

2. So what is an index?

Let’s 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. speed.

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

• The primary key is to identify the uniqueness of database records. 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 index outside the primary key index can be empty.

• The primary key can also be composed of multiple fields to form a composite primary key, and 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.

The above is the detailed content of Primary key and index relationship in MySQL. For more information, please follow other related articles on the PHP Chinese website!