1. An error is reported when mysql deletes the primary key in the table, as shown in the following code:
mysql> alter table student drop primary key; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
2. Cause of the problem:
When viewing the types of the student table, it is found that there is an auto increment type option in the primary key column. To delete the primary key in the table, you need to delete the auto increment type first. The code is as follows:
mysql> desc student; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | MUL | NULL | | | age | tinyint(2) | NO | MUL | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.02 sec)
3. Delete the auto increment type above the primary key column in the student table. The code is as follows:
mysql> alter table student change id id int; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
Tip: the alter table student change id int; command modifies the type of the column in the student table, and the auto increment type will be deleted naturally.
4. Check the type of student table and find that the auto increment type has been deleted. The code is as follows:
mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | name | char(20) | NO | MUL | NULL | | | age | tinyint(2) | NO | MUL | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
5. Delete the primary key in the student. The code is as follows:
mysql> alter table student drop primary key; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0
6. Finally, check the type of student table and find that the primary key in the table has been deleted. The code is as follows:
mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | name | char(20) | NO | MUL | NULL | | | age | tinyint(2) | NO | MUL | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)