![SQL Logo](https://www.tutorialspoint.com/sql/images/sql-mini-logo.jpg)
- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Database
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 | +-----+----------+-----+-----------+----------+