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

In this issue, the editor will bring you about the difference between the primary key and the index in mysql. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

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 unique indexes in a table, 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 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.

Several common problems:

1. What is the primary key?

Let me tell you something specific. Everyone has read the book. If you haven't read it, look for it to see if there is a page number on each page. The main key of our data sheet is equivalent to this page number. See?

two。 So what is the index?

We also take the book, the index is equivalent to the book catalog, with the catalog we can quickly know the basic content and structure of the book, data index, it can speed up the query speed of the data table.

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

The primary key is to identify the uniqueness of the database record, the record is not allowed to repeat, 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.

Using the primary key database automatically creates a primary index, or you can create an index on a non-primary key to facilitate query efficiency.

The index can improve the query speed, which is equivalent to the directory of the dictionary, through which you can quickly query the desired results without the need for a full table scan.

The value of an index outside the primary key index can be empty.

The primary key can also be composed of multiple fields to form a compound primary key, and the primary key must also be the only index.

A unique index indicates that the index value is unique and can be composed of one or more fields, and a table can have multiple unique indexes.

The above is the difference between the primary key and the index in mysql shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are 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.