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

Arushi

Published on 16-Feb-2018 14:59:47

• Related Questions & Answers
• What happens if I will add a UNIQUE constraint on the same column for multiple times?
• MySQL ALTER column to remove primary key and auto_increment?
• How do I drop a primary key in MySQL?
• What happens if I will delete a row from MySQL parent table?
• What is the difference between MySQL PRIMARY KEY and UNIQUE constraint?
• How can we remove PRIMARY KEY constraint from a column of an existing MySQL table?
• What happens when I insert the value ‘NULL’ in an AUTO_INCREMENT MySQL column?
• How to change a primary key in MySQL to auto_increment?
• What will happen if the MySQL AUTO_INCREMENT column reaches the upper limit of the data type?
• What happens with the trigger when we will drop the table having that trigger?
• Is it compulsory to set PRIMARY KEY for AUTO_INCREMENT value?
• How can I define a column of a MySQL table PRIMARY KEY without using the PRIMARY KEY keyword?
• What happens if we try to extend a final class in java?
• What happens if I will use integer values as arguments of MySQL LOCATE() function?
• What happens if I will prepare the statement with the same name without de-allocating the earlier one?