SQL Auto Increment

Sometime in a table, we need a column to automatically increment its value on each insertion of a new row. Most of the time, this column with the auto-increment feature may be the column with a primary key like ID, S.No., etc.

So to accomplish this task, SQL provides "AUTO_INCREMENT," which can be added after the column name while creating the table in this way. Let me first write the general form that shows how we can add the auto-increment feature to a column while creating the table. Then I will write the actual code.

CREATE TABLE tableName (
   column1Name column1DataType AUTO_INCREMENT,
   column2Name column2DataType,
   column3Name column3DataType,
   .
   .
   .
);

For example, to add the auto-increment feature to a column named "customerId" while creating the table named "mytable." Here is the SQL code snippet.

CREATE TABLE mytable (
   customerId int AUTO_INCREMENT,
   .
   .
   .
);

And to add the auto-increment feature with a primary key for a specific column, here is the SQL code snippet that may help to achieve this task.

CREATE TABLE mytable (
   customerId int AUTO_INCREMENT,
   .
   .
   .
   PRIMARY KEY (customerId)
);

There is another scenario where we need to implement the auto-increment feature for a column. That may be for an existing table. Because sometime, as a beginner or for other reasons, a table either already existed or we just created it but forgot to add the auto-increment to any specific column. So in that case, you need to add auto-increment without disturbing the table.

So to achieve this task, use the ALTER TABLE command or statement. Here is its general form to add auto-increment to an existing table:

ALTER TABLE tableName MODIFY columnName columnDataType NOT NULL AUTO_INCREMENT;

In the above SQL query, the NOT NULL is a SQL constraint; when used, the column always needs a value. Now here is an example of the SQL query that adds auto-increment to a column named "studentId" available in the table "student."

ALTER TABLE student MODIFY studentId int NOT NULL AUTO_INCREMENT;

Since a column with the "auto-increment" feature starts with 1 and increments its value by 1 on each row insertion or addition, there is another scenario where we need our auto-increment column to start at a particular number and then continue incrementation by 1. So to do this, here is the general form:

ALTER TABLE tableName AUTO_INCREMENT=valueToStart;

For example:

ALTER TABLE student AUTO_INCREMENT=5000;

After executing the above SQL query, the auto-increment column will now start with 5000.

Knowing how to add the auto-increment feature to a column is similar to knowing how to insert data into the table. Because most of the time we require at least one column with the auto-increment feature in order to uniquely identify each row while retrieving the record or performing other tasks that require row uniqueness.

SQL Online Test