- SQL Tutorial
- SQL Tutorial
- SQL Display Data
- SQL Update Records
- SQL Delete Record
- SQL Alter Table
- SQL Join Tables
- SQL Auto Increment
- SQL Drop Table/Database
- Computer Programming
- Learn Python
- Python Keywords
- Python Built-in Functions
- Python Examples
- Learn C++
- C++ Examples
- Learn C
- C Examples
- Learn Java
- Java Examples
- Learn C#
- Learn Objective-C
- Web Development
- Learn HTML
- Learn CSS
- Learn PHP
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;
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.
« Previous Topic Next Topic »