A preliminary study on composite primary key and joint primary key in SQL statements

1. Composite primary key

The so-called composite primary key means that the primary key of your table consists of more than one field, and does not use an auto-increment ID with no business meaning as the primary key.
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 duplicate names, so you need to add the ID field to ensure the uniqueness of your records. Generally speaking, the field length and number of the primary key should be as small as possible.

Here there will be a doubt? The primary key is the only index, so why can a table create multiple primary keys?

In fact, the statement "primary key is the only index" is a bit ambiguous. For example, we create an ID field in the table, automatically increase it, and set it as the primary key. There is no problem with this because "the primary key is a unique index" and the automatic increase of the ID ensures uniqueness, so it is possible.

At this point, we create another field name, of type varchar, and set it as the primary key. You will find that you can fill in the same name value in multiple rows of the table. Doesn’t this violate the statement that "the primary key is the only index"?

That's why I said "primary key is the only index" is ambiguous. It should be "When there is only one primary key in the table, it is a unique index; when there are multiple primary keys in the table, it is called a composite primary key, and the composite primary key jointly guarantees a unique index."

Why is a composite primary key needed when the self-incrementing ID can be used as a unique primary key? Because not all tables need to have an ID field. For example, if we create a student table and there is no ID that can uniquely identify the students, what should we do? The student's name, age, and class may be repeated, and a single field cannot be used to uniquely identify them. At this time, we can set multiple fields as primary keys to form a composite primary key. These multiple fields jointly identify uniqueness. Among them, it is no problem if some primary key field values ​​are repeated, as long as all the primary key values ​​of multiple records are not exactly the same, they are not considered duplicates.

2. Joint primary key

As the name implies, a composite primary key is a combination of multiple primary keys (primary keys are unique in principle, so don't be bothered by unique values.)

The significance of joint primary key: using two fields (or multiple fields, the following specific combination of two fields) to determine a record means that these two fields are not unique and can be repeated separately. The advantage of this setting is that you can intuitively see the number of records of a repeated field.

A Simple Example

Primary key A and primary key B form a joint primary key

The data of primary key A and primary key B can be exactly the same. The union lies in the fact that the joint primary key formed by primary key A and primary key B is unique.

In the following example, the data of primary key A is 1, and the data of primary key B is also 1. The joint primary key is actually 11. This 11 is a unique value, and the unique value 11 is absolutely not allowed to appear again. (This is a many-to-many relationship)

Primary key A data Primary key B data

1 1
twenty two
3 3

The maximum value of the joint primary key of primary key A and primary key B is

11
12
13
twenty one
twenty two
twenty three
31
32
33

Summary: In my opinion, a composite primary key is composed of more than one field, such as ID+name, ID+phone, etc., while a joint primary key is a combination of the subjects of two tables at the same time. This is the biggest difference from the composite primary key!

The above is all the content of this article about the initial exploration of composite primary keys and joint primary keys in SQL statements. I hope it will be helpful to everyone. Interested friends can refer to: Several important MySQL variables, Detailed explanation of MySQL prepare principles, Introduction to MySQL methods for deleting table data with foreign key constraints, etc. If you have any questions, you can leave a message at any time, and we can discuss and make progress together.