SQL: Auto increment a field

‘AUTO INCREMENT’ keyword is used to generate a unique number when new record is inserted.

 

What is the starting value of AUTO INCREMENT field?

Default value is 1, whenever a new record is inserted, auto increment field will be incremented by 1.

 

Example

CREATE TABLE employee (

    emp_id int NOT NULL AUTO_INCREMENT,

    first_name varchar(255) NOT NULL,

    last_name varchar(255),

    PRIMARY KEY (emp_id)

);

 

mysql> CREATE TABLE employee (
    ->     emp_id int NOT NULL AUTO_INCREMENT,
    ->     first_name varchar(255) NOT NULL,
    ->     last_name varchar(255),
    ->     PRIMARY KEY (emp_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

 

Let’s insert a record and confirm that emp_id is auto populated.

mysql> INSERT INTO employee(first_name, last_name) VALUES ('krishna', 'gurram');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM employee;
+--------+------------+-----------+
| emp_id | first_name | last_name |
+--------+------------+-----------+
|      1 | krishna    | gurram    |
+--------+------------+-----------+
1 row in set (0.00 sec)

 

As you observe the output, emp_id for the record is populated with value 1.

 

Let’s add one more record.

mysql> INSERT INTO employee(first_name, last_name) VALUES ('Gopi', 'Battu');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM employee;
+--------+------------+-----------+
| emp_id | first_name | last_name |
+--------+------------+-----------+
|      1 | krishna    | gurram    |
|      2 | Gopi       | Battu     |
+--------+------------+-----------+
2 rows in set (0.01 sec)

 

Can I specify AUTO_INCREMENT sequence to start with another value?

Yes, you can do that. For example below statement sets auto increment field to start from 100.

 

ALTER TABLE employee AUTO_INCREMENT=100;

mysql> ALTER TABLE employee AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO employee(first_name, last_name) VALUES ('Sailu', 'Dokku');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM employee;
+--------+------------+-----------+
| emp_id | first_name | last_name |
+--------+------------+-----------+
|      1 | krishna    | gurram    |
|      2 | Gopi       | Battu     |
|    100 | Sailu      | Dokku     |
+--------+------------+-----------+
3 rows in set (0.00 sec)

 

From the output, you can confirm that the new record start with sequence number 100.

 

 

 

Previous                                                    Next                                                    Home