Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you about the difference between the joint primary key and the compound primary key. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

I. compound primary key

The so-called compound primary key means that the primary key of your table contains more than one field and does not use self-increasing 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 above name and id fields is the compound primary key of your test table, it appears because your name field may have a duplicate name, so add the ID field to ensure the uniqueness of your record. In general, the length and number of fields in the primary key should be as few as possible.

There's going to be a doubt? The primary key is the only index, so why can a table create multiple primary keys?

In fact, the phrase "the primary key is the only index" is somewhat ambiguous. For example, we create an ID field in the table, which automatically grows and sets it as the primary key, which is no problem, because "the primary key is the only index", and ID auto-growth ensures uniqueness, so it can.

At this point, we create a field name, which is of type varchar and is also set 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 against the saying that the primary key is the only index?

That's why I said that "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 is more than one primary key in the table, it is called the compound primary key, and the compound primary key jointly ensures a unique index."

Why self-growing ID can already be used as a unique identification of the primary key, why do you still need a compound primary key? Because, not all tables need to have an ID field. For example, if we build a student table and there is no ID that can uniquely identify the student, what should we do? the name, age and class of the student may be duplicated and cannot be uniquely identified by a single field. In this case, we can set multiple fields as primary keys to form a compound primary key, where the multiple fields are identified uniquely. There is no problem that several primary key field values are duplicated, as long as all primary key values of multiple records are not exactly the same.

Second, joint primary key

As the name implies, a federated primary key means that multiple primary keys are combined to form a primary key combination (the primary key is unique in principle, don't be bothered by unique values.)

The meaning of the joint primary key: use 2 fields (or more fields, followed by a combination of 2 fields) to determine a record, indicating that these two fields are not unique, and the two fields can be repeated separately. The advantage of this setting is that you can directly see the number of records in a duplicate field.

A simple example:

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

The data of primary key An and primary key B can be exactly the same, and the union is that the joint primary key formed by primary key An and primary key B is unique.

The following example primary key A data is 1, primary key B data is also 1, the joint primary key is actually 11, this 11 is the only value, it is absolutely not allowed to appear 11 this unique value. (this is the many-to-many relationship.)

Primary key A data primary key B data

1 1

2 2

3 3

The joint primary key value of primary key An and primary key B is at most

eleven

twelve

thirteen

twenty-one

twenty-two

twenty-three

thirty-one

thirty-two

thirty-three

Summary: in my opinion, the compound primary key is composed of more than one field, such as ID+name,ID+phone, etc., while the federated primary key should be a combination of the topics of two tables at the same time. This is the biggest difference with the compound primary key!

The above is the difference between the joint primary key and the compound primary key shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.