What happens if I will try to drop PRIMARY KEY constraint from the AUTO_INCREMENT column?
As we know the AUTO_INCREMENT column must have the PRIMARY KEY constraint on it also hence when we will try to drop PRIMARY KEY constraint from the AUTO_INCREMENT column the MySQL returns an error message regarding the incorrect table definition. The example below will demonstrate it −
Example
Suppose we have ‘Accounts’ table having 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 is having a filed ‘Sr’ with AUTO_INCREMENT and PRIMARY KEY definition. Now, if we will try to drop this PRIMARY KEY then MySQL will throw an error as follows −
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
Published on 16-Feb-2018 19:29:47
