The difference between union primary key and composite primary key

1. Composite primary key

#The so-called composite primary key means that the primary key of your table contains more than one field. If it is not used, it has no business meaning. The auto-incrementing id is used 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 the same name, so you need to add The ID field can ensure the uniqueness of your record. Generally, the field length and number of fields of the primary key should be as small as possible.

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

In fact, "the primary key is the only index" is a bit ambiguous. For example, we create an ID field in the table, grow it automatically, and set it as the primary key. This is no problem because "the primary key is the only index" and the ID field automatically grows to ensure uniqueness, so it is OK.

At this point, we create another field name with 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. Isn’t this illegal? Is this sentence "the primary key is the only index"?

That's why I said "the primary key is the only index" is ambiguous. It should be "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 composite primary keys guarantees a unique index".

Why self-increasing ID can already be 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 ID that uniquely identifies the student, what should we do? The student's name, age, and class may all be repeated, and a single field cannot be used to Unique identification. 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, there is no problem if certain primary key field values ​​are repeated, as long as there are not multiple records. If all primary key values ​​are exactly the same, they are not considered duplicates.

2. Joint primary key

Joint primary key, as the name suggests, is the combination of multiple primary keys to form a primary key combination (the primary key is in principle unique , don’t be troubled by unique values.)

The meaning of the joint primary key: use 2 fields (or multiple fields, specifically a combination of 2 fields will be used later) to determine a record, indicating that these 2 fields The fields are not unique. The two fields 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 primary key A and primary key B. The resulting joint primary key is unique.
In the following example, the data of primary key A is 1, the data of primary key B is also 1, and the combined primary key is actually 11. This 11 is a unique value, and the unique value of 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 　　##2　　　2
3　　3

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

11

12
13
21
22
23
31
32
33

Summary : From my point of view, a composite primary key consists of more than one field, such as ID name, ID phone, etc., while a joint primary key must be a combination of the themes of two tables at the same time. This is the biggest difference from the composite primary key!

For more related questions, please visit the PHP Chinese website:

mysql video tutorial

The above is the detailed content of The difference between union primary key and composite primary key. For more information, please follow other related articles on the PHP Chinese website!