Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "mysql primary key and index what is the difference", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn the "mysql primary key and index what is the difference between" this article.

Differences: 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 there can be 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.

The relational database depends on the primary key, which is the cornerstone of the database physical schema. The primary key has only two uses at the physical level:

Uniquely identifies a row.

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

The primary key (primary key) uniquely identifies the attribute or attribute group of a row in the table. A table can have only 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, and the database management system automatically generates a unique index for the primary key, so the primary key is also a special index.

An index is a special file (an index on an InnoDB data table is a part of the table space) that contains reference pointers to all records in the data table. The index is used to quickly find those records with specific values, mainly for the convenience of retrieval, to speed up the access speed, to create according to certain rules, and generally play a sorting role.

Here are some differences and relationships between primary keys and indexes.

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

A primary key is an attribute or attribute group that can uniquely identify a row in a 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, you can ensure that the error of the wrong object does not occur when performing data updates and deletions. In addition to the above functions, primary keys often form referential integrity constraints with foreign keys to prevent data inconsistencies. When designing the database, the primary key plays a very important role.

The primary key can ensure that the record is unique and the primary key field is 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.

two。 There can be multiple candidate indexes in a table, but only one primary key.

3. Null values are not allowed in primary key columns, while null values are allowed in index columns.

4. Indexes can improve the speed of queries.

In fact, both the primary key and the index are 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, while the index actually exists in the database, and the primary key is generally built. It is mainly used to avoid having the same records in a table, and the index can generally not be built, but if you need to query the table, it is best to build, which can speed up the retrieval.

The above is all the content of this article entitled "what is the difference between mysql primary key and index". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.