Is the primary key an index in mysql? 03/31 Update SLTechnology News&Howtos

Is the primary key an index in mysql?

2025-03-31 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

Editor to share with you whether the primary key in mysql is an index. I hope you will get something after reading this article. Let's discuss it together.

The primary key in mysql is not an index. The full name of the primary key is "primary key constraint", which is a constraint on the data in the table, which is a special field of the table, which can uniquely identify each piece of information in the table, while the index is a special database structure. it is composed of one or more columns in the data table and can be used to quickly query records with a specific value in the data table.

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

MySQL primary key

In MySQL, 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.

Constraint is a kind of constraint on the data in the table, which can help the database administrator to manage the database better and ensure the correctness and validity of the data in the database.

A primary key is a special field of a table that uniquely identifies each piece of information in the table. For example, the student number in the student information table is unique.

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.

Set primary key constraints when creating a table

When you set a primary key constraint when you create a data table, you can set either a primary key for one field in the table or a federated primary key for multiple fields in the table. But no matter which method is used, there can be only one primary key in a table. The following describes how to set up a single-field primary key and a multi-field joint primary key.

1) set single-field primary key

In the CREATE TABLE statement, the primary key is specified by the PRIMARY KEY keyword.

Specify the primary key while defining the field. The syntax format is as follows:

PRIMARY KEY [default]

Example 1

Create a tb_emp3 data table in the test_db database with the primary key id,SQL statement and the run result as follows.

Or specify the primary key after all the fields have been defined. The syntax format is as follows:

[CONSTRAINT] PRIMARY KEY [field name]

Example 2

Create a tb_emp4 data table in the test_db database with the primary key id,SQL statement and the run result as follows.

2) set the federated primary key when creating the table

The so-called federated primary key is that the primary key is made up of multiple fields in a table.

For example, when setting up the student course selection data table, do you use the student number as the primary key or the course number as the primary key? If you use the student number as the key, then a student can only choose one course. If you use the course number as the key, only one student can take a course. Obviously, both of these situations are not in line with the actual situation.

In fact, the design of students' course selection schedule should be limited to the fact that a student can only choose the same course once. Therefore, the student number and course number can be put together as the primary key, which is the joint primary key.

The primary key is composed of multiple fields, and the syntax format is as follows:

PRIMARY KEY [Field 1, Field 2, … , field n]

Note: when the primary key consists of multiple fields, you cannot declare the primary key constraint directly after the field name.

Example 3

Create the data table tb_emp5, assuming that there is no primary key id in the table. In order to uniquely identify an employee, you can combine name and deptId as the primary key. The SQL statement and run results are as follows.

Add a primary key constraint when modifying a table

Primary key constraints can not only be created while the table is created, but can also be added when the table is modified. It is important to note, however, that null values are not allowed in fields that are set as primary key constraints.

The syntax format for adding primary key constraints when modifying a datasheet is as follows:

ALTER TABLE ADD PRIMARY KEY ()

View the table structure, SQL statements, and run results of the tb_emp2 data table as shown below.

Example 4

Modify the data table tb_emp2 and set the field id as the primary key. The SQL statement and run result are as follows.

In general, when you set a primary key constraint for a field in a table when you modify a table, make sure that the values in the fields that are set as primary key constraints cannot be duplicated and that they are not empty. Otherwise, the primary key constraint cannot be set.

MySQL Index (Index)

Index is a special database structure, which is composed of one or more columns in a data table, which can be used to quickly query records with a specific value in the data table.

Through the index, when querying data, you do not have to read all the information of the record, but only query the index column. Otherwise, the database system will read all the information from each record to match.

The index can be compared to the sequence table of Xinhua Dictionary. For example, to look up the word "library", if you don't use phonetic order, you need to look it up page by page from 400 pages of the dictionary. However, if you extract pinyin to form a sequence table, you only need to find it directly from the sequence table of more than 10 pages. This can save a lot of time.

Advantages and disadvantages of index

Index has its obvious advantages and inevitable disadvantages.

Advantages

The advantages of the index are as follows:

The uniqueness of each row of data in the database table can be guaranteed by creating a unique index.

Indexes can be set on all MySQL column types.

It can greatly speed up the query speed of data, which is the main reason for using indexes.

The connection between tables can be accelerated in terms of achieving the referential integrity of data.

The time of grouping and sorting in a query can also be significantly reduced when using grouping and sorting clauses for data queries.

Shortcoming

Adding an index also has many disadvantages, mainly as follows:

It takes time to create and maintain index groups, and the time spent increases as the amount of data increases.

Indexes need to take up disk space, in addition to data tables occupy data space, each index also takes up a certain amount of physical space. If there are a large number of indexes, the index file may reach the maximum file size faster than the data file.

When the data in the table is added, deleted and modified, the index should also be maintained dynamically, which reduces the speed of data maintenance.

When using an index, you need to consider the advantages and disadvantages of the index.

Indexes can improve query speed, but affect the speed at which records are inserted. Because, when inserting records into an indexed table, the database system sorts by index, which reduces the speed of inserting records, and the impact on the speed of inserting a large number of records will be more obvious. In this case, the best way is to delete the index in the table, then insert the data, and then create the index after the insertion is complete.

After reading this article, I believe you have a certain understanding of "whether the primary key in mysql is an index". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.