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

MySQLMySQLi Database

<p style="">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 &minus;</p><h2 style="">Example</h2><p>Suppose we have &lsquo;Accounts&rsquo; table having the following description &minus;</p><pre class="prettyprint notranslate">mysql&gt; Describe accounts; +--------+-------------+------+-----+---------+----------------+ | Field &nbsp;| Type &nbsp; &nbsp; &nbsp; &nbsp;| Null | Key | Default | Extra &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| +--------+-------------+------+-----+---------+----------------+ | Sr &nbsp; &nbsp; | int(10) &nbsp; &nbsp; | NO &nbsp; | PRI | NULL &nbsp; &nbsp;| auto_increment | | Name &nbsp; | varchar(20) | YES &nbsp;| &nbsp; &nbsp; | NULL &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| | amount | int(15) &nbsp; &nbsp; | YES &nbsp;| &nbsp; &nbsp; | NULL &nbsp; &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| +--------+-------------+------+-----+---------+----------------+ 3 rows in set (0.10 sec)&nbsp;</pre><p>It is having a filed &lsquo;Sr&rsquo; 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 &minus;</p><pre class="prettyprint notranslate">mysql&gt; Alter table Accounts DROP PRIMARY KEY; ERROR 1075 (42000): Incorrect table definition; there can be only one &nbsp; &nbsp;auto column and it must be defined as a key</pre>
raja
Updated on 19-Jun-2020 11:53:40

Advertisements