What does mysql composite primary key mean?

In MySQL, a composite primary key means that the primary key of a database table contains more than one field, that is, a multi-field primary key; a composite primary key is a type of primary key and has the same functions and definitions as the primary key. The composite primary key cannot contain unnecessary redundant columns; when a column of the composite primary key is deleted, if the primary key composed of the remaining columns still satisfies the uniqueness principle, then the composite primary key is incorrect.

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

What is the composite primary key of a data table?

The so-called composite primary key means that the primary key of your table contains more than one field.

MySQL multi-field primary key can also become a composite primary key. A composite primary key is also a type of primary key and has the same functions and definitions as the primary key.

Primary key constraints define a primary key in the table to uniquely determine the identifier of each row of data in the table. The primary key can be a certain column in the table or a combination of multiple columns. A primary key composed of multiple columns is called a composite primary key.

Compound primary keys cannot contain unnecessary redundant columns. When a column of the composite primary key is deleted, if the primary key composed of the remaining columns still satisfies the uniqueness principle, then the composite primary key is incorrect. This is the principle of minimization.

For example:

create table test
(
name varchar(19),
id number,
value varchar(10),
primary key (name,id)
)

The combination of the name and id fields above is the composite primary key of your test table

It appears because your name field may have the same name. , so you need to add the ID field to ensure the uniqueness of your record

Why does the composite primary key appear?

This is because the table name field may have the same name. situation, so we need to add the id field to ensure the uniqueness of the record. In our development, it is better to keep one primary key field. If we have to create two composite primary keys, we should look for one when creating the table. A field that uniquely identifies the object.

Then the question comes again. Doesn’t it mean that a table can only have one primary key? The primary key is the only index of a table, so why can a table create multiple primary keys?

In fact, "the primary key is the only index" is a bit ambiguous. Here is an example: We are used to having an id field in the table, which is set to auto-grow and set as the primary key. At this time "The primary key is the only index", and the ID automatically grows to ensure uniqueness. At this time, when a field name varchar(50) is created, it is also set as the primary key. At this time, id and name become the composite primary key, and the name field in the table can insert the same name value when inserting data. There is another problem at this time. Isn't it said that "the primary key is a unique index? How can it have the same value?"

So saying "the primary key is a unique index" is ambiguous; the primary key is a unique index The premise is that "when there is only one primary key in the table, it is the only index; when there are multiple primary keys in the table, it is called a composite primary key. The combination of the composite primary keys guarantees a unique index, which can be understood as the combination of the composite primary keys. A unique index (primary key) of a table". Why is the self-increasing ID already used as the primary key for unique identification? Why is a composite primary key still needed? Because not all tables must have the ID field. For example, if we build a student table and there is no unique ID that can identify students, what should we do? A student's name, age, and class may be repeated and cannot be uniquely identified using a single field. In this case, we can set multiple fields as primary keys to form a composite primary key. These multiple fields jointly identify uniqueness. Among them, certain There is no problem with duplicate primary key field values. As long as all primary key values ​​in multiple records are exactly the same, they are not considered duplicates.

Summary: In development, it is best to keep one primary key field. If you have to create two composite primary keys, then when creating the table, you should look for a field that can uniquely identify the object. . In short, use less composite primary keys.

How to add a composite primary key?

You can add a composite primary key when creating a table. At this time, the primary key is composed of multiple fields. The syntax rules are as follows:

PRIMARY KEY [字段1，字段2，…,字段n]

Example: Create the data table tb_emp, assuming that there is no Primary key id, in order to uniquely identify an employee, you can combine name and deptId as the primary key

mysql> CREATE TABLE tb_emp
    -> (
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(id,deptId)
    -> );
Query OK, 0 rows affected (0.37 sec)

Joint primary key

The difference between a joint primary key and a composite primary key is that the joint primary key is reflected in multiple tables, and the composite primary key is reflected in multiple fields in a table.

学生表：student
create table student(
id int auto_increment comment '主键id',
name varchar(30) comment '姓名',
age smallint comment '年龄',
primary key(id)
);

课程表：course
create table course(
id int auto_increment comment '主键id',
name varchar(30) comment '课程名称',
primary key(id)
);

学生课程表：stu_course
create table IF NOT EXISTS stu_cour(
id int  auto_increment comment '主键id',
stu_id mediumint comment '学生表id',
cour_id mediumint comment '课程表id',
primary key(id)
);

At this time, the id in stu_course represents the joint primary key, and a record of students and courses can be obtained through the id

So the joint primary key, as the name suggests, is the combination of multiple primary keys to form a primary key combination, which is reflected in the union. (Primary keys are unique in principle, don’t be troubled by unique values) Indexes can greatly improve the speed of data query, but they will slow down the speed of inserting, deleting, and updating tables, because when performing these write operations, the index must be operated. document. Simple example: Primary key A and primary key B form a joint primary key C. The data of primary key A and primary key B can be exactly the same. The union is that the joint primary key C formed by primary key A and primary key B is unique.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What does mysql composite primary key mean?. For more information, please follow other related articles on the PHP Chinese website!