What happens if I try to remove the PRIMARY KEY constraint from the AUTO_INCREMENT column?

As we know, AUTO_INCREMENT column must also have PRIMARY KEY constraint, so when we try to remove PRIMARY KEY constraint from AUTO_INCREMENT column, MySQL returns incorrect information about the table Defined error message. The following example will demonstrate it −

Example

Suppose we have a table called “Accounts” with the following description −

mysql> Describe accounts;

+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| Sr     | int(10)     | NO   | PRI | NULL    | auto_increment |
| Name   | varchar(20) | YES  |     | NULL    |                |
| amount | int(15)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

3 rows in set (0.10 sec) 

It has a table with AUTO_INCREMENT and Field 'Sr' defined by PRIMARY KEY. Now, if we try to delete this PRIMARY KEY, MySQL will throw the following error -

mysql> Alter table Accounts DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one
   auto column and it must be defined as a key

The above is the detailed content of What happens if I try to remove the PRIMARY KEY constraint from the AUTO_INCREMENT column?. For more information, please follow other related articles on the PHP Chinese website!