我们可以通过使用 add 关键字和 alter table 语句对现有表的多列设置 primary key 约束。
示例假设我们有一个表“room_allotment”如下 -
mysql> create table room_allotment(id int, name varchar(20), roomno int);query ok, 0 rows affected (0.20 sec)mysql> describe room_allotment;+--------+-------------+------+-----+---------+-------+| field | type | null | key | default | extra |+--------+-------------+------+-----+---------+-------+| id | int(11) | yes | | null | || name | varchar(20) | yes | | null | || roomno | int(11) | yes | | null | |+--------+-------------+------+-----+---------+-------+3 rows in set (0.11 sec)
现在我们可以使用以下查询在多个列“id”和“name”上添加复合主键
mysql> alter table room_allotment add primary key(id, name);query ok, 0 rows affected (0.29 sec)records: 0 duplicates: 0 warnings: 0mysql> describe room_allotment;+--------+-------------+------+-----+---------+-------+| field | type | null | key | default | extra |+--------+-------------+------+-----+---------+-------+| id | int(11) | no | pri | 0 | || name | varchar(20) | no | pri | | || roomno | int(11) | yes | | null | |+--------+-------------+------+-----+---------+-------+3 rows in set (0.12 sec)
从上面的结果集中可以看出，多列都添加了primary key。
以上就是我们如何在现有 mysql 表的多个列上设置 primary key？的详细内容。