How to change composite primary key to single 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 an auto-incrementing 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 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. Fields can ensure the uniqueness of your records. 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 created an ID field in the table, automatically grew it, and set it as the primary key. This is no problem because "primary key is the only index", and the ID field automatically grows to ensure uniqueness. , so yes.

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, and the combination of the 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.

How to change the composite primary key to a single primary key

A table can only have one primary key:

Primary key based on one column :

alter table test add constraint PK_TEST primary key(ename);

United primary key based on multiple columns:

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

The above is the detailed content of How to change composite primary key to single primary key. For more information, please follow other related articles on the PHP Chinese website!