What is the difference between mysql primary key and index

Difference: 1. The primary key is used to uniquely identify the attributes or attribute groups of a row in the table, while the index is used to quickly find records with specific values; 2. A table can only have one primary key, but it can have Multiple candidate indexes; 3. The primary key column does not allow null values, while the index column allows null values; 4. The primary key is a logical key, and the index is a physical key.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

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:

• uniquely identifies a row.

• As an object that can be effectively referenced by foreign keys.

The primary key is an attribute or attribute group that uniquely identifies a row in a table. A table can only have one primary key, but can have multiple candidate indexes. Primary keys often form referential integrity constraints with foreign keys to prevent data inconsistencies. 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.

Index (index) is a special file (the index on the InnoDB data table is a component of the table space). They contain reference pointers to all records in the data table. Indexes are used to quickly find records with specific values. They are mainly for the convenience of retrieval and to speed up access. They are created according to certain rules and generally play a sorting role.

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 candidate indexes in a table, but there can only be one primary key.

3. Primary key columns do not allow null values, while 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 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.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is the difference between mysql primary key and index. For more information, please follow other related articles on the PHP Chinese website!