What if I forgot to set Auto Increment? Can I set it later in MySQL?

Yes, you can set Auto Increment later with ALTER table. Let us first create a table. Here, as you can see, we haven’t set Auto Increment −

mysql> create table forgetToSetAutoIncrementDemo
   -> (
   -> StudentId int,
   -> StudentName varchar(30)
   -> );
Query OK, 0 rows affected (1.17 sec)

Now check the table description, there is no auto_increment column −

mysql> desc forgetToSetAutoIncrementDemo;

This will produce the following output −

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| StudentId   | int(11)     | YES  |     | NULL    |       |
| StudentName | varchar(30) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Following is the query to set auto increment on column StudentId −

mysql> alter table forgetToSetAutoIncrementDemo modify column StudentId int NOT NULL
AUTO_INCREMENT PRIMARY KEY;
Query OK, 0 rows affected (2.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

Now check the table description once again, auto_increment column has been added successfully −

mysql> desc forgetToSetAutoIncrementDemo;

This will produce the following output −

+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| StudentId   | int(11)     | NO   | PRI | NULL    | auto_increment |
| StudentName | varchar(30) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Following is the query to insert some records in the table using insert command −

mysql> insert into forgetToSetAutoIncrementDemo(StudentName) values('Larry');
Query OK, 1 row affected (0.16 sec)

mysql> insert into forgetToSetAutoIncrementDemo(StudentName) values('Chris');
Query OK, 1 row affected (0.13 sec)

mysql> insert into forgetToSetAutoIncrementDemo(StudentName) values('Robert');
Query OK, 1 row affected (0.17 sec)

Following is the query to display all records from the table using a select statement −

mysql> select * from forgetToSetAutoIncrementDemo;

This will produce the following output displaying StudentID as auto_increment −

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 1         | Larry       |
| 2         | Chris       |
| 3         | Robert      |
+-----------+-------------+
3 rows in set (0.00 sec)

Nishtha Thakur

e

Updated on: 30-Jul-2019

99 Views

Kickstart Your Career

Get certified by completing the course

Get Started