MS SQL Training (32 Blogs) Become a Certified Professional
AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (30 Blogs)
  • SQL Essentials Training and Certification (4 Blogs)
SEE MORE

How to use Auto Increment in SQL?

Last updated on Jun 19,2023 126K Views

31 / 37 Blog from Introduction to SQL

Databases are known to store a humongous amount of data in a logical format. But, have you ever thought about a situation, wherein you have to mention a unique number for every new record in a table? Well, I think, it’s practically impossible to enter the numbers manually. So, instead, you can use Auto Increment in SQL, to automatically enter a unique number for every new record in the table.

SQL - Auto Increment in SQL - EdurekaThe following topics will be covered in this article:

  1. What is auto increment in SQL?
  2. How do you set up Auto Increment?

What is auto increment in SQL?

I am sure the name suggests its functionality by itself. Auto Increment is a field used to generate a unique number for every new record added into a table. This is generally used for the primary key column as it becomes easy for the developers to automatically generate a unique number for every new record.

Now, that you know, what is auto increment in SQL, let us discuss how to use this field in various DBMS.

How do you set up Auto Increment?

For your better understanding, I will consider the following table:

CustomerIDCustomerNameAgePhoneNumber

If you wish to know how to create a table, you can refer to my article on CREATE TABLE command. .Let us start with the syntax and examples of the Auto Increment field in different DBMS.

Syntax and Example for SQL Server

To use the auto increment field, in SQL Server, you have to use the IDENTITY keyword. 

Syntax: 

CREATE TABLE TableName (
Column1 DataType  IDENTITY(starting value, increment by),
Column2 DataType, 
);

Example:

Create a table with the name Customers, and columns CustomerID, CustomerName, Age and PhoneNumber. Here, auto-increment the CustomerID and make it the primary key for the table.

CREATE TABLE Customers (
CustomerID int IDENTITY(1,1) PRIMARY KEY,
CustomerName varchar(255),
Age int,
PhoneNumber int);

In the above example, the starting value for IDENTITY is 1 and it should increment by 1 for every new record added. You can mention these values, according to your wish. Also, to insert values in the above table, you have to use the INSERT query in the following way:

INSERT INTO Customers (CustomerName,Age, PhoneNumber)
VALUES ('Abhay','25','9876543210');

Here, if you observe, I have not mentioned the CustomerID column, as the ID will be automatically generated. So, if you see insert 4 more values using the below queries:

INSERT INTO Customers (CustomerName,Age, PhoneNumber)
VALUES ('Sonal','22','9812313210');
 
INSERT INTO Customers (CustomerName,Age, PhoneNumber)
VALUES ('Anuj','19','9956413210');
 
INSERT INTO Customers (CustomerName,Age, PhoneNumber)
VALUES ('Mona','24','9876543911');
 
INSERT INTO Customers (CustomerName,Age, PhoneNumber)
VALUES ('Sanjay','31','9657154310');

Then, you will see the below output:

CustomerIDCustomerNameAgePhoneNumber
1Abhay259876543210
2Sonal229812313210
3Anuj199956413210
4Mona249876543911
5Sanjay319657154310

Next, in this article on auto increment in SQL, let us see how to auto-increment a column in MySQL.

Syntax and Example for MySQL

To use the auto increment field, in MySQL, you have to use the AUTO_INCREMENT keyword. The starting value for AUTO_INCREMENT is 1 by default, and it will increment by 1 for each new record.

Syntax: 

CREATE TABLE TableName (
Column1 DataType  AUTO_INCREMENT,
Column2 DataType, 
);

Example:

Create a table with the name Customers, and columns CustomerID, CustomerName, Age and PhoneNumber. Here, auto-increment the CustomerID and make it the primary key for the table.

CREATE TABLE Customers (
CustomerID int AUTO_INCREMENT PRIMARY KEY,
CustomerName varchar(255),
Age int,
PhoneNumber int);

If you wish to start the AUTO_INCREMENT value by any other number, then you can use the keyword in the following way:

Syntax: 

ALTER TABLE TableName AUTO_INCREMENT=50;

Example:

ALTER TABLE Customers AUTO_INCREMENT=50;

Similar to that of SQL Server, you can INSERT values into the table, by using the INSERT statement. On inserting values, you will see the same output, like that of the above table. Next, in this article on auto increment in SQL, let us see how to auto-increment a column in MS Access.

Syntax and Example for MS Access

To use the auto increment field, in MS Access, you have to use the AUTOINCREMENT keyword. 

Syntax: 

CREATE TABLE TableName (
Column1 DataType AUTOINCREMENT,
Column2 DataType,
);

Example:

Create a table with the name Customers, and columns CustomerID, CustomerName, Age and PhoneNumber. Here, auto-increment the CustomerID and make it the primary key for the table.

CREATE TABLE Customers (
CustomerID int AUTOINCREMENT PRIMARY KEY,
CustomerName varchar,
Age int,
PhoneNumber int);

The default starting value of AUTOINCREMENT is 1 and it will also increment by 1 for each record. But, if you wish to change this, and let us say, you want to set the starting value to be 20 and increment by 2, you can use the auto-increment feature as below:

AUTOINCREMENT(20,2)

Find out our MS SQL Course in Top Cities

IndiaIndia
SQL Training in BangaloreSQL Course in Pune
SQL Training in ChennaiSQL Course in Mumbai
SQL Training in hyderabadSQL Course in Kolkata

Similar to that of SQL Server, you can INSERT values into the table, by using the INSERT statement. On inserting values, you will see the same output, as that of the above table. Next, in this article on auto increment in SQL, let us see how to auto-increment a column in Oracle.

Syntax and Example for Oracle

To use the auto increment field, in Oracle, you have to create an auto-increment field with the sequence object. The sequence object generates a number sequence.

Syntax to create a sequence:

CREATE SEQUENCE name_of_sequence
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

In the above syntax,

  1. Name_of_sequence – Creation of sequence named name_of_sequence
  2. START – Mentions the starting value
  3. INCREMENT BY – Mentions the value incremented by
  4. CACHE – Mentions the maximum number of values to be stored for faster access.

Example:

Create a sequence object where the starting value is 1, is incremented by 3, and a maximum number of values to be stored is 20. 

CREATE SEQUENCE seq_customers
MINVALUE 1
START WITH 1
INCREMENT BY 3
CACHE 20;

Discover the secrets of efficient data management through our SQL Online Course.

Similar to that of MySQL and SQL Server, you can INSERT values into the table, by using the INSERT statement. On inserting values, you will see the same output, as that of the above table. Next, in this article on auto increment in SQL, let us see how to auto-increment a column in PostgreSQL.

Syntax and Example for PostgreSQL

To use the auto increment field, in PostgreSQL, you have to create an auto-increment field with the sequence object. The sequence object generates a number sequence.

Syntax:

CREATE TABLE TableName (
Column1 DataType  SERIAL PRIMARY KEY,
Column2 DataType, 
);

Example:

Create a table with the name Customers, and columns CustomerID, CustomerName, Age and PhoneNumber. Here, auto-increment the CustomerID and make it the primary key for the table.

CREATE TABLE Customers (
CustomerID int SERIAL PRIMARY KEY,
CustomerName varchar(255),
Age int,
PhoneNumber int);

Similar to that of MySQL, SQL Server, and other DBMS you can INSERT values into the table, by using the INSERT statement. On inserting values, you will see the same output, like that of the above table.

With this, we come to an end to this article on Auto Increment in SQL. I hope you understood how to use the above commands. If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of this article on “Auto Increment in SQL” and I will get back to you.

Upcoming Batches For Microsoft SQL Course
Course NameDateDetails
Microsoft SQL Course

Class Starts on 13th April,2024

13th April

SAT&SUN (Weekend Batch)
View Details
Microsoft SQL Course

Class Starts on 22nd June,2024

22nd June

SAT&SUN (Weekend Batch)
View Details
Comments
1 Comment
  • Sayyed Ashfak says:

    Thanks

Join the discussion

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.

image not found!
image not found!

How to use Auto Increment in SQL?

edureka.co