

- 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
Alter a MySQL column to be AUTO_INCREMENT?
Let’s say we have a table and now there is a requirement to add AUTO_INCREMENT on column name. For that, use the MODIFY command.
Here, we will create a demo table first.
mysql> create table AddingAutoIncrement -> ( -> Id int, -> Name varchar(200), -> Primary key(Id) -> ); Query OK, 0 rows affected (0.47 sec)
We have created a table above and now let us alter the table to add AUTO_INCREMENT on column name ‘Id’. The syntax is as follows −
alter table yourTableNamet modify yourColumnName int AUTO_INCREMENT;
Apply the above syntax to add AUTO_INCREMENT. The query is as follows.
mysql> ALTER table AddingAutoIncrement modify Id int AUTO_INCREMENT; Query OK, 0 rows affected (1.19 sec) Records: 0 Duplicates: 0 Warnings: 0
Above, we have added “AUTO_INCREMENT” on column name ‘Id’. Let us check the same with the help of DESC command. The query is as follows −
mysql> desc AddingAutoIncrement;
Sample Output.
+-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | Id | int(11) | NO | PRI | NULL | auto_increment | | Name | varchar(200) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
Look at the output above and column name ‘Extra’. In the column name ‘Extra’, there is a keyword auto_increment. This itself says that we have added the keyword successfully.
Now, I am going to insert records and check whether the row is increment by one or not. The query is as follows −
mysql> insert into AddingAutoIncrement(Name) values('John'); Query OK, 1 row affected (0.20 sec) mysql> insert into AddingAutoIncrement(Name) values('Smith'); Query OK, 1 row affected (0.12 sec) mysql> insert into AddingAutoIncrement(Name) values('Bob'); Query OK, 1 row affected (0.10 sec)
Display all records with the help of SELECT statement.
mysql> select *from AddingAutoIncrement;
The following is the output.
+----+-------+ | Id | Name | +----+-------+ | 1 | John | | 2 | Smith | | 3 | Bob | +----+-------+ 3 rows in set (0.00 sec)
As you can see in the above output, row is incremented by 1.
- Related Questions & Answers
- MySQL ALTER column to remove primary key and auto_increment?
- How to find if a column is auto_increment in MySQL?
- How to add column using alter in MySQL?
- Alter a table column from VARCHAR to NULL in MySQL
- How do I alter a MySQL table column defaults?
- AUTO_INCREMENT in MySQL can be signed by default?
- How to alter a MySQL Column from varchar(30) to varchar(100)?
- How to insert own values into auto_increment column in MySQL?
- How can we apply AUTO_INCREMENT to a column?
- What MySQL returns when we alter AUTO_INCREMENT value which is less than current sequence number?
- How to alter the data type of a MySQL table’s column?
- How to alter column type of multiple columns in a single MySQL query?
- Insertion in a MySQL table with only a single column set as auto_increment?
- Reset AUTO_INCREMENT in MySQL
- MySQL AUTO_INCREMENT with Examples