How to change a compound primary key to a single primary key by mysql

This article is about how mysql changes a compound primary key to a single primary key. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

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.

such as

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.

How to change a compound primary key to a single primary key

A table can have only one primary key:

Based on a column of primary keys:

Alter table test add constraint PK_TEST primary key (ename)

A federated primary key based on multiple columns:

Alter table test add constraint PK_TEST primary key (ename,birthday)

Thank you for reading! About mysql how to change the compound primary key to a single primary key to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.