mysql-添加删除主键(7)

删除主键采用drop关键字，添加主键采用add关键字，具体命令格式如下：

alter table 表名 drop primary key; --删除主键
alter table 表名 add primary key(字段名,...);  --增加主键

--重新创建一个表,下面的测试在这个表上面完成
mysql> create table test_key(
    -> id int unsigned not null auto_increment key,
    -> name varchar(30) not null unique)engine=innodb charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> desc test_key;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)      | NO   | UNI | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

--注意下一条命令，使用drop删除test_key表上的primary key；但是失败了，
mysql> alter table test_key drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

----不是因为命令有问题，而是因为字段id上面有auto_increment属性，
----有这个属性的字段，mysql强制有key约束，所以删除失败了
----所以如果需要删除带有auto_increment属性的primary key，需要先用
----modify或者change关键字删除auto_increment属性，再然后使用drop删除主键

mysql> alter table test_key modify id int unsigned not null;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test_key drop primary key;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_key;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   |     | NULL    |       |
| name  | varchar(30)      | NO   | PRI | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
----在使用add primary key(字段名)命令将id字段的primary key加上.
mysql> alter table test_key add primary key(id); 
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_key;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(30)      | NO   | UNI | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)