Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- 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.
1K+ Views
