After setting a primary key in a table, what should I do if I want to delete the primary key? The following article will introduce to you how to delete the primary key in MySQL. I hope it will be helpful to you.
First let’s take a look at the syntax of deleting the primary key:
ALTER TABLE TABLE_NAME DROP PRIMARY KEY;
There are two situations to consider when deleting the primary key in MySQL:
1. The primary key column does not have any constraints and the primary key can be deleted directly.
Example:
mysql> create table test1_3( -> id int not null primary key, -> name char(10) -> ); Query OK, 0 rows affected (0.01 sec)
We can directly use drop to delete the primary key
mysql> alter table test1_3 drop primary key; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
2. If It is the primary key of auto-increment (AUTO_INCREMENT attribute)
Example:
mysql> create table test1_2( -> id int not null auto_increment, -> name char(10),-> primary key(id) -> ); Query OK, 0 rows affected (0.00 sec) mysql> desc test1_2; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
If deleted directly, an error will be reported
mysql> alter table test1_2 drop primary key;
Output:
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key #这说明此列是自动增长列,无法直接删除
The attributes of the column are still With AUTO_INCREMENT, you must first remove the automatic growth attribute of this column before you can delete the primary key.
mysql> alter table test1_2 modify id int; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table test1_2 drop primary key; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
The above is the detailed content of How to delete primary key in mysql?. For more information, please follow other related articles on the PHP Chinese website!