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

This article introduces the knowledge of "what is the difference between mysql unique index and primary key". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Differences: 1, the primary key is a constraint, the unique index is a kind of index; 2, the primary key creation must contain a unique index, the unique index is not necessarily the primary key; 3, the unique index column allows null values, the primary key is not allowed; 4, the primary key can be cited as a foreign key by other tables, the unique index cannot; 5, a table can only create one primary key, but can create multiple unique indexes.

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

The full name of primary key (PRIMARY KEY) is "primary key constraint", which is the most frequently used constraint in MySQL. In general, in order to make it easier for DBMS to find records in the table faster, a primary key is set in the table.

Primary keys are divided into single-field primary keys and multi-field joint primary keys. This section will explain the creation, modification, and deletion of these two primary key constraints.

When using primary keys, you should pay attention to the following points:

Only one primary key can be defined per table.

The primary key value must uniquely identify each row in the table and cannot be NULL, that is, there can be no two rows of data with the same primary key value in the table. This is the principle of uniqueness.

A field name can only appear once in the federated primary key field table.

The federated primary key cannot contain unnecessary extra fields. When a field of the federated primary key is deleted, the federated primary key is incorrect if the primary key formed by the remaining fields still satisfies the principle of uniqueness. This is the principle of minimization.

The difference between primary key and unique index

1. The primary key is a kind of constraint, and the unique index is a kind of index. The two are different in nature.

2. After the primary key is created, it must contain a unique index, and the unique index is not necessarily the primary key.

3. The unique index column allows null values, while the primary key column does not allow null values.

4. A primary key can be referenced as a foreign key by other tables, but a unique index cannot.

5. A table can only create at most one primary key, but multiple unique indexes can be created.

6. The primary key is more suitable for those unique identifiers that are not easy to change, such as automatic increment column, ID number and so on.

7. In RBO mode, the execution plan of the primary key takes precedence over the unique index. Both can improve the speed of query.

This is the end of the content of "what is the difference between the mysql unique index and the primary key". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.