SQL - Auto Increment

---

When we are working with large databases in SQL, several tables and data fields will require unique numbers. For example, a table column with a PRIMARY KEY or UNIQUE constraint will always require a new number. You can do this manually to a limited extent.

But when it comes to huge databases, you might forget the last unique number you entered or simply enter the same number twice because it's not easy to remember everything. Apart from the memory problem, giving all records a unique number is also a tedious task. This is where auto-increment in SQL comes in to solve the ambiguity or duplicity of the key.

What is auto increment in SQL?

An auto increment in SQL is a function that is specified to a field so that it can automatically generate and provide a unique value for each record you enter into a SQL table. This field is often used as a PRIMARY KEY column where you must enter a unique value for each record you add. However, it can also be used for UNIQUE constraint columns.

How to use Auto Increment in SQL?

Auto increment automatically generate unique numbers for the records you enter into the database table. Several databases support auto-increment fields. You will walk through some of the primary DBMS systems and see how to set up an auto-increment column. You will need to create a CUSTOMERS table in all DBMSs and use the auto-increment in SQL.

Syntax

Following is the syntax to pass an auto increment in a column −

CREATE TABLE table_name (ID PRIMARY KEY AUTO_INCREMENT = initial_value);

Following is the syntax to add an auto-increment into the existing table in SQL −

ALTER TABLE table_name AUTO_INCREMENT = initial_value;

initial_value − It is the initial value from which you want to start numbering. If we do not pass the initial value, then it will begin with the default value, i.e. 1.

Example

Following is the query to create a table named "Customers" that accepts ID as an auto-increment −

CREATE TABLE CUSTOMERS(
   ID INT PRIMARY KEY AUTO_INCREMENT,
   NAME VARCHAR (20) NOT NULL
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

Following is an output of the above query −

Query OK, 0 rows affected (0.03 sec)

To insert the rows, we use the following query −

insert INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES("Ramesh", 32, "Ahmedabad", 2000.00);
insert INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES("Khilan", 25, "Delhi", 1500.00);
insert INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES("kaushik", 23, "Kota", 2000.00);
insert INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES("Chaitali", 25, "Mumbai", 6500.00);

Following is an output of the above query −

Query OK, 4 row affected (0.00 sec)

Fetching Values

To view the table data, we use the following SELECT query −

select * from CUSTOMERS;

Output

Following is an output of the above query, where ID will generate automatically because of auto increment −

+----+----------+-----+-----------+---------+
| ID | NAME     | AGE | ADDRESS   | SALARY  |
+----+----------+-----+-----------+---------+
|  1 | Ramesh   |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan   |  25 | Delhi     | 1500.00 |
|  3 | kaushik  |  23 | Kota      | 2000.00 |
|  4 | Chaitali |  25 | Mumbai    | 6500.00 |
+----+----------+-----+-----------+---------+
4 rows in set (0.00 sec)

Example

In the following example, we are passing the auto-increment to the existing table named "Customers" that we earlier created and passing the default value of 100, so ID will start at 100.

Use the syntax to start numbering from 100 in the CUSTOMERS table and then insert more rows to see the action.

Following is the query that starts incrementing from 100 in the existing table named "Customers" −

ALTER TABLE CUSTOMERS AUTO_INCREMENT = 100;

Following is an output of the above query −

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Inserting more rows into the table −

We are inserting more rows to see the action of the auto increment.

insert INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES("Hardik", 27, "Bhopal", 8500.00);
insert INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES("Komal", 22, "MP", 4500.00);
insert INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES("Muffy", 24, "Indore", 10000.00);

Following is an output of the above query −

Query OK, 3 row affected (0.00 sec)

Viewing table

To view the above table data, we use the following SELECT query −

select * from CUSTOMERS;

Output

The output of the above query is shown below. It shows the auto increment in action. We are getting the ID's value, which follows the ID 4 and begins at 100.

+-----+----------+-----+-----------+----------+
| ID  | NAME     | AGE | ADDRESS   | SALARY   |
+-----+----------+-----+-----------+----------+
|   1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|   2 | Khilan   |  25 | Delhi     |  1500.00 |
|   3 | kaushik  |  23 | Kota      |  2000.00 |
|   4 | Chaitali |  25 | Mumbai    |  6500.00 |
| 100 | Hardik   |  27 | Bhopal    |  8500.00 |
| 101 | Komal    |  22 | MP        |  4500.00 |
| 102 | Muffy    |  24 | Indore    | 10000.00 |
+-----+----------+-----+-----------+----------+