Composite primary key and Union primary key

one, compound primary key

The so-called compound primary key means that your table's primary key contains more than one field composition, and does not use an ID with no business meaning as the primary key.

Like what

CREATE TABLE Test 
( 
   name varchar, 
   ID number, 
   value varchar (), 
   primary KEY (Name,id) 

The name and ID fields above are combined with the composite primary key of your test table. It appears because your Name field may have duplicate names, so add the ID field so you can guarantee the uniqueness of your records, in general, the primary key field length and the number of fields is the better.

Is there going to be a doubt here? The primary key is the only index, so why can a table create multiple primary keys?

In fact, "primary key is the only index" is somewhat ambiguous. For example, we create an ID field in a table that automatically grows and sets the primary key, which is fine because "primary key is the only index" and automatic ID growth guarantees uniqueness, so you can.

At this point, we create a field name, the type is varchar, also set the primary key, you will find that in the multiple rows of the table you can fill in the same name value, this is not a violation of the "primary key is the only index" this sentence.

So I said, "primary key is the only index" is ambiguous. It should be "a unique index when there is only one primary key in the table, and a composite primary key that is combined to guarantee a unique index when there are multiple primary keys in the table."

Why do I need a composite primary key since the growth ID can already be the primary key that uniquely identifies? Because, not all tables have to have ID this field, for example, we build a student table, no one can identify the student ID, how to do it, the student's name, age, class can be repeated, can not use a single field to uniquely identify, at this time, we can set multiple fields as the primary key to form a composite primary key, The combination of these fields identifies uniqueness, where a number of primary key field values are duplicated without any problems, as long as all primary key values that are not multiple records are identical.

two, joint primary key

A joint primary key, as the name suggests, is the combination of multiple primary keys to form a primary key group (primary keys are unique in principle, not to be bothered by unique values.) )

The meaning of a federated primary key: with 2 fields (or more fields, the following is a specific combination of 2 fields to determine a record, that the 2 fields are not unique, 2 fields can be repeated separately, so the advantages of setting, you can very intuitive see a repeating field of the number of records.

A simple example

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

Primary key A can be exactly the same data as primary key B, and the Union is unique between primary key A and primary key B, the joint primary key.
The following example primary key a data is 1, the primary key B data is also 1, the Union primary key is actually 11, this 11 is the unique value, absolutely does not have to be allowed to appear again 11 this unique value. (This is a many-to-many relationship)

Primary key A data primary key B data
1 1
2 2
3 3

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

11
12
13
21st
22
23
31
32
33

Summary: In my opinion, a composite primary key is composed of more than one field, such as Id+name,id+phone, and the combined primary key is combined with two-table themes. This is the biggest difference with the composite primary key!