A Preliminary Study on the combination of primary keys and primary keys of SQL statements; a preliminary study on the primary keys of SQL statements

A Preliminary Study on the combination of primary keys and primary keys of SQL statements; a preliminary study on the primary keys of SQL statements

I. Composite primary key

The so-called composite primary key means that the primary key of your table contains more than one field and does not use auto-increment IDs without business meanings as the primary key.
For example

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

The above name and id fields are combined as the composite primary key of your test table. It appears because your name field may have duplicate names, therefore, you need to add the ID field to ensure the uniqueness of your record. Generally, the smaller the field length and number of primary keys, the better.

Is there a doubt here? A primary key is a unique index. Why can multiple primary keys be created in a table?

In fact, the "primary key is the only index" is a bit ambiguous. For example, we have created an ID field in the table to automatically increase and set it as the primary key. This is correct because "the primary key is a unique index ", the automatic growth of IDS ensures uniqueness, so yes.

At this point, we create another field name with the varchar type and the primary key. You will find that you can enter the same name value in multiple rows of the table, isn't this against the phrase "primary key is the only index?

That's why I say that "primary key is the only index" is ambiguous. It should be "when the table has only one primary key, it is a unique index; when the table has multiple primary keys, it is called a composite primary key, composite primary key joint to ensure a unique index ".

Why does the auto-increment ID have to be used as the primary key of the unique identifier. Because not all tables need the ID field. For example, if we create a student table and there is no unique student ID, what should we do, the names, ages, and classes of students 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 fields are associated with the identification and uniqueness. It is no problem to duplicate the values of some primary key fields. As long as not all primary key values of multiple records are identical, they are not repeated.

Ii. federated primary keys

As the name suggests, a federated primary key is a combination of multiple primary keys to form a primary key combination (the primary key is unique in principle and should not be troubled by the unique value .)

Meaning of joint primary key: two fields (or multiple fields followed by a combination of two fields) are used to determine a record. It indicates that these two fields are not unique, the two fields can be repeated separately. With this setting, you can intuitively view the number of records of a repeated field.

A simple example

Primary Key A and primary key B constitute the joint primary key

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

In the following example, the primary key A data is 1, the primary key B data is also 1, and the joint primary key is actually 11. This 11 is the unique value, and it is definitely not sufficient to show the unique value of 11 again. (This is the many-to-many relationship)

Primary Key A data primary key B Data

1 1
2 2
3 3

The Union primary key value between primary key A and primary key B is the most

11
12
13
21
22
23
31
32
33

Summary:In my opinion, the composite primary key is composed of more than one field, such as ID + name, ID + phone. The composite primary key must be a combination of the topics of both tables. This is the biggest difference from the composite primary key!

The above is all the content about the SQL statement compound primary key and joint primary key. I hope it will be helpful to you. For more information, see: MySQL variables, MySQL prepare principles, MySQL table data deletion methods with foreign key constraints, etc. If you have any questions, feel free to leave a message, let's discuss and make progress together.