- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What happens if I will try to drop PRIMARY KEY constraint from the AUTO_INCREMENT column?
<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 −</p><h2 style="">Example</h2><p>Suppose we have ‘Accounts’ table having the following description −</p><pre class="prettyprint notranslate">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) </pre><p>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 −</p><pre class="prettyprint notranslate">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</pre>
Updated on 19-Jun-2020 11:53:40
- Related Questions & Answers
- What happens if I will add a UNIQUE constraint on the same column for multiple times?
- How to make MySQL table primary key auto increment?
- Two columns as primary key with auto-increment in MySQL?
- 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 if I forgot to set Auto Increment? Can I set it later in MySQL?
- How can I define a column of a MySQL table PRIMARY KEY without using the PRIMARY KEY keyword?
- What happens with the trigger when we will drop the table having that trigger?
- Set the MySQL primary keys auto increment to be unlimited (or incredibly huge)?
- MySQL query to set my auto increment column ( id ) to zero or reset the value of auto increment field?
- 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?
- How to get primary key value (auto-generated keys) from inserted queries using JDBC?
Advertisements