SQL: Modify existing column as auto increment

Step 1: Create student table.

 

CREATE TABLE student(

    student_id int,

    first_name varchar(20) NOT NULL,

    last_name varchar(20) NOT NULL,

    email varchar(20) NOT NULL

);

mysql> CREATE TABLE student(
    ->     student_id int,
    ->     first_name varchar(20) NOT NULL,
    ->     last_name varchar(20) NOT NULL,
    ->     email varchar(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> desc student;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| student_id | int         | YES  |     | NULL    |       |
| first_name | varchar(20) | NO   |     | NULL    |       |
| last_name  | varchar(20) | NO   |     | NULL    |       |
| email      | varchar(20) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

 

Step 2: Insert some data into table student.

 

INSERT INTO student(student_id, first_name, last_name, email) VALUES (123, 'krishna', 'gurram', 'krishna@abcdef.com');

INSERT INTO student(student_id, first_name, last_name, email) VALUES (523, 'gopi', 'battu', 'gopi@abcdef.com');

INSERT INTO student(student_id, first_name, last_name, email) VALUES (63, 'sailu', 'ptr', 'ptr@abcdef.com');

mysql> INSERT INTO student(student_id, first_name, last_name, email) VALUES (123, 'krishna', 'gurram', 'krishna@abcdef.com');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO student(student_id, first_name, last_name, email) VALUES (523, 'gopi', 'battu', 'gopi@abcdef.com');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO student(student_id, first_name, last_name, email) VALUES (63, 'sailu', 'ptr', 'ptr@abcdef.com');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT * FROM student;
+------------+------------+-----------+--------------------+
| student_id | first_name | last_name | email              |
+------------+------------+-----------+--------------------+
|        123 | krishna    | gurram    | krishna@abcdef.com |
|        523 | gopi       | battu     | gopi@abcdef.com    |
|         63 | sailu      | ptr       | ptr@abcdef.com     |
+------------+------------+-----------+--------------------+
3 rows in set (0.00 sec)

 

Step 3: Lets apply ‘AUTO_INCREMENT’ command to student table.

 

ALTER TABLE student MODIFY student_id int UNSIGNED PRIMARY KEY AUTO_INCREMENT;

mysql> ALTER TABLE student MODIFY student_id int UNSIGNED PRIMARY KEY AUTO_INCREMENT;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

  Let’s add some more records and check the behaviour of student_id column.

 

mysql> INSERT INTO student(first_name, last_name, email) VALUES ('chamu', 'M', 'chamu@abcdef.com');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM student;
+------------+------------+-----------+--------------------+
| student_id | first_name | last_name | email              |
+------------+------------+-----------+--------------------+
|         63 | sailu      | ptr       | ptr@abcdef.com     |
|        123 | krishna    | gurram    | krishna@abcdef.com |
|        523 | gopi       | battu     | gopi@abcdef.com    |
|        524 | chamu      | M         | chamu@abcdef.com   |
+------------+------------+-----------+--------------------+
4 rows in set (0.00 sec)

 

As you see the output, 524 is assigned to student_id (Since 523 is the maximum student_id number in student table, new ids will start from here).

 

You can execute the command ‘desc student;’ to confirm ‘auto increment’ is applied on the table student.

 

mysql> desc student;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| student_id | int unsigned | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(20)  | NO   |     | NULL    |                |
| last_name  | varchar(20)  | NO   |     | NULL    |                |
| email      | varchar(20)  | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

 

 

 

 

 

 

Previous                                                    Next                                                    Home