How to add composite primary key in mysql?

In mysql, you can add a composite primary key when creating a table. The syntax rule is "PRIMARY KEY [field 1, field 2,..., field n]", which means that the primary key is composed of multiple fields.

The primary key constraint defines a primary key in the table to uniquely determine the identifier of each row of data in the table. The primary key can be a certain column in the table or a combination of multiple columns. A primary key composed of multiple columns is called a composite primary key.

A composite primary key cannot contain unnecessary redundant columns. When a column of the composite primary key is deleted, if the primary key composed of the remaining columns still satisfies the uniqueness principle, then the composite primary key is incorrect. This is the principle of minimization.

How to add a composite primary key?

You can add a composite primary key when creating a table. At this time, the primary key is composed of multiple fields. The syntax rules are as follows:

PRIMARY KEY [字段1，字段2，…,字段n]

Example: Create the data table tb_emp, assuming that there is no Primary key id, in order to uniquely identify an employee, name and deptId can be combined as the primary key

mysql> CREATE TABLE tb_emp
    -> (
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(id,deptId)
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC tb_emp;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(25) | NO   | PRI | NULL    |       |
| deptId | int(11)     | NO   | PRI | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.14 sec)

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