What Is Auto-Increment in SQL?

In this article, we’ll learn the basics of the auto-increment feature of SQL: what it is and how to use it efficiently.

One of the many features offered by SQL is auto-increment. It allows us to automatically generate values in a numeric column upon row insertion. You could think of it as an automatically supplied default value – the next number in a number sequence – that’s generated when the row is inserted. It’s most commonly used for a primary key column because this column must uniquely identify each row and the auto-increment feature ensures this condition is fulfilled.

As there are different implementations of the auto-increment feature in various databases – such as MySQL, SQL Server, Oracle, PostgreSQL – I am going to describe the auto-increment syntax and usage in each of them. We’ll also touch upon the sequence objects available in Oracle and PostgreSQL databases.

Implementing Auto-Increment in MySQL

Let’s imagine we have created a Person table that contains, among others, a PersonIdentityNumber column:

PersonIdentityNumber	FirstName	LastName	Age
…	…	…	…

We want this PersonIdentityNumber column to store a unique number per person. Furthermore, all values of the PersonIdentityNumber column must come from a number sequence. To achieve this, we can set up an auto-increment for this column.

First, let’s insert a few rows into the Person table, giving values for all the columns except for the PersonIdentityNumber column.

INSERT INTO Person (FirstName, LastName, Age)
VALUES
('Brad', 'Cooper', 25),
('Amy', 'Smith', 30),
('Roger', 'Anderson', 27);

When selecting all the rows of the Person table, you’ll see that the PersonIdentityNumber column contains unique numbers from a number sequence that identifies each row.

	SELECT * FROM Person;

PersonIdentityNumber	FirstName	LastName	Age
1	Brad	Cooper	25
2	Amy	Smith	30
3	Roger	Anderson	27

By default, the auto-increment feature starts numbering the rows from 1 and increments it by 1 on each row insertion; thus, the row numbers are 1, 2, 3, 4, and so on.

Note: I’ve assumed that you know all about the CREATE TABLE statement, but if you need to get a better grasp on how to create a table in SQL , see How to Create a Table in SQL.

The auto-increment feature is implemented at the time of table creation. Let’s look at the way MySQL does this:

CREATE TABLE Person (
PersonIdentityNumber INTEGER AUTO_INCREMENT,
FirstName VARCHAR(30),
LastName VARCHAR(30),
Age INTEGER,
PRIMARY KEY(PersonIdentityNumber)
);

The syntax above shows the CREATE TABLE statement for the Person table. To use the auto-increment feature on the PersonIdentityNumber column, we must define this column as the PRIMARY KEY for the table.

Let’s look at another implementation example in MySQL.

We have a sketch of the Products table shown below.

ProductId	ProductName	QuantityInStock
…	…	…

We want the ProductId column to fulfill the following conditions:

• It should be filled in automatically as the rows are inserted in the Products table.
• The ProductId numbers are assigned in order of products’ insertion into the database.

To achieve this, we use this CREATE TABLE statement:

CREATE TABLE Products (
ProductId INTEGER NOT NULL AUTO_INCREMENT,
ProductName VARCHAR(30),
QuantityInStock INTEGER,
PRIMARY KEY(ProductId)
);

Now that the ProductId column uses the auto-increment feature, we can insert values for the ProductName and QuantityInStock columns and get the ProductId column values automatically generated for us.

INSERT INTO Products (ProductName, QuantityInStock)
VALUES
('Apple', 500),
('Banana', 200),
('Pear', 300);

SELECT * FROM Products;

ProductId	ProductName	QuantityInStock
1	Apple	500
2	Banana	200
3	Pear	300

Note that to use the auto-increment feature for a column X in MySQL, this column X must be declared PRIMARY KEY for the given table.

Primary and foreign keys are standard features of SQL that closely relate to the auto-increment functionality. For more details, please read our articles What is a Primary Key in SQL? and What is a Foreign Key in SQL?.

Auto-Increment in SQL Server Using the IDENTITY Data Type

The auto-increment feature in Microsoft SQL Server database differs in syntax and also slightly in the offered functionalities from MySQL. Let’s look at an example using the Animal table with the Id column as the auto-increment column.

First, we create the Animal table and use the IDENTITY data type to make the Id column auto-generated.

CREATE TABLE Animal (
Id INTEGER IDENTITY,
Name VARCHAR(30),
Age INTEGER
);

Now we can insert the values and then select all the rows from the Animal table to check how the auto-increment worked.

INSERT INTO Animal(Name, Age)
VALUES
('Monkey', 3),
('Dog', 5),
('Cat', 7);

SELECT * FROM Animal;

Id	Name	Age
1	Monkey	3
2	Dog	5
3	Cat	7

It worked!

Let’s discuss the IDENTITY data type in greater detail. If you want to customize the starting number and the increment, this data type can be declared with two arguments, as shown below:

We could declare the Animal table as follows:

CREATE TABLE Animal (
Id INTEGER IDENTITY(100, 10),
Name VARCHAR(30),
Age INTEGER
);

That would give us the following result table (after running the INSERT statement):

SELECT * FROM Animal;

Id	Name	Age
100	Monkey	3
110	Dog	5
120	Cat	7

This feature is useful when we want to have a custom number assigned to each row – one that obeys the mathematical rules set in the IDENTITY data type.

Auto-Increment in PostgreSQL Using the SERIAL Data Type

The PostgreSQL database uses the SERIAL data type to implement the auto-increment feature. This data type uses a SEQUENCE object – discussed in the following section – to generate the values for a numeric column of that type.

Let’s look at an example that uses the Months table. First, we create the Months table with its Id column of the SERIAL type and its Name column of the VARCHAR type:

CREATE TABLE Months (
Id SERIAL,
Name VARCHAR
);

After inserting the values into the Months table and selecting all its rows, we get the following:

INSERT INTO Months(Name)
VALUES
('January'),
('February'),
('March');
    
SELECT * FROM Months;

Id	Name
1	January
2	February
3	March

You might have noticed that the SERIAL data type in PostgreSQL works like the IDENTITY (IDENTITY(1, 1)) data type in SQL Server. Does it also allow changes to the starting and increment values? Yes.

As mentioned at the beginning of this section, the SERIAL data type is based on the SEQUENCE object that provides the values for the column of SERIAL data type. By modifying the SEQUENCE object, we can alter the starting and increment values for a SERIAL column.

It’s essential you know how to find the sequence associated with a specific column. There is a special function in PostgreSQL called pg_get_serial_sequence() for this. Let’s see how to use it:

SELECT pg_get_serial_sequence('Months', 'Id') as SequenceName;

SequenceName
public.months_id_seq

Please note that the months_id_seq sequence is the default sequence assigned to the SERIAL-type column.

Let’s move to the next section to find out more about the SEQUENCE objects available in PostgreSQL and Oracle databases.

SEQUENCE Objects and Auto-Increments

The SEQUENCE objects are used as background components of the auto-increment feature in Oracle and PostgreSQL databases.

SEQUENCE allows users to generate sequential numbers based on predefined rules stored in this object. Such rules include:

• Starting value – The first value of the sequence.
• Increment value – The value by which each next value is incremented.
• Min value – The smallest value that can appear in the sequence.
• Max value – The largest value that can appear in the sequence.
• Cache – How many numbers are cached, i.e. stored in memory for faster access.
• Cycles – Indicates if cycles are allowed, i.e. if, after reaching its max/min value, the sequence can be restarted.

A SEQUENCE object can be removed by using the DROP SEQUENCE statement. This is also the easiest way to reset the sequence, i.e. by recreating it.

To better understand what a sequence is, let’s look at the syntax for creating a SEQUENCE object and SEQUENCE usage for both databases.

SEQUENCE Object in PostgreSQL

Let’s create a SEQUENCE object and investigate all the attributes used. Here’s the code:

CREATE SEQUENCE my_first_sequence
START WITH 100
INCREMENT BY 10
NO MINVALUE
MAXVALUE 150
CACHE 2
NO CYCLE;

The sequence name is my_first_sequence and the values stored by it must conform to the following rules:

• The sequence starts with 100.
• Each next number is incremented by 10, i.e. 100, 110, 120, etc.
• There is no minimum value because the sequence is ascending – you could consider the start value to be the minimum value for this sequence.
• The maximum value is 150.
• There are no cycles allowed; if you try to fetch the next value after 150, you’ll get the error message “55000: nextval: reached maximum value of sequence "my_first_sequence" (150)”.

So the sequence my_first_sequence stores the values 100, 110, 120, 130, 140, and 150.

Now that we have created our first sequence in PostgreSQL, let’s use it. Look at the implementation of this sequence within this table:

CREATE TABLE MyFirstSequence (
Id INTEGER NOT NULL DEFAULT nextval('my_first_sequence'),
Name VARCHAR
);

Now we can insert values into the MyFirstSequence table to see how the implemented sequence works.

INSERT INTO MyFirstSequence(Name) 
VALUES 
('First Sequence Number'), 
('Second Sequence Number'), 
('Third Sequence Number'), 
('Fourth Sequence Number');

SELECT * FROM MyFirstSequence;

Id	Name
100	First Sequence Number
110	Second Sequence Number
120	Third Sequence Number
130	Fourth Sequence Number

Please note that by limiting the sequence to six numbers, you are only allowed to use auto-increment to insert six rows into the MyFirstSequence table. However, you could insert more rows by directly specifying Id column values in the INSERT statement.

Here’s another typical sequence usage example. We will start from 5 and increase each next value by 1. There will be no MAXVALUE defined for this sequence:

CREATE SEQUENCE my_other_sequence
START WITH 5
INCREMENT BY 1;

The my_other_sequence sequence produces the following values: 5, 6, 7, 8, 9, 10, and so on, up to infinity:

SELECT nextval('my_other_sequence') as SequenceNextValue;

SequenceNextValue
5

SELECT nextval('my_other_sequence') as SequenceNextValue;

SequenceNextValue
6

SELECT nextval('my_other_sequence') as SequenceNextValue;

SequenceNextValue
7

Try implementing the my_other_sequence sequence on your own table, following the my_first_sequence example, to get a better grasp on using this.

SEQUENCE Object in Oracle

The Oracle database offers very similar syntax for creating SEQUENCE objects. Let’s create a sequence in the Oracle database:

CREATE SEQUENCE employees_sequence
START WITH 1
INCREMENT BY 1
MAXVALUE 10
NOCYCLE
NOCACHE;

As you may expect, this sequence starts from 1 and goes up to 10, increasing by 1.

Oracle allows us to access the values of the sequence using the NEXTVAL and CURRVAL methods. Let’s see how they work. We’ll start with NEXTVAL, which fetches the value that will be assigned next:

SELECT employees_sequence.NEXTVAL FROM DUAL;

NEXTVAL
1

SELECT employees_sequence.NEXTVAL FROM DUAL;

NEXTVAL
2

SELECT employees_sequence.NEXTVAL FROM DUAL;

NEXTVAL
3

The DUAL table is created automatically by Oracle to fetch data on the fly, as shown in the example above.

Likewise, the CURRVAL fetches the current value of the sequence.

CREATE TABLE Orders (
Id INTEGER,
Name VARCHAR(40)
);

INSERT INTO Orders 
VALUES
(employees_sequence.CURRVAL, 'Order #2345');

INSERT INTO Orders 
VALUES
(employees_sequence.NEXTVAL, 'Order #4567');

SELECT * FROM Orders;

Id	Name
3	Order #2345
4	Order #4567

The usage of SEQUENCE objects slightly differs for Oracle databases. However, the result is the same – values are generated automatically by the SEQUENCE object.

Throughout this article, we’ve been creating tables, using various data types, and inserting values into tables. If you’re new to these concepts, the course Creating Database Structure will help you get a working knowledge of them.

Now, let’s leave Oracle and Postgres and go to the standard SQL version of auto-increment.

SQL Standard Syntax: GENERATED ALWAYS AS IDENTITY / GENERATED BY DEFAULT AS IDENTITY

The SQL standards GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY are both supported by Oracle and PostgreSQL, while MySQL only supports GENERATED ALWAYS AS.

Let’s see what each one does.

GENERATED ALWAYS AS IDENTITY

The SQL standard GENERATED ALWAYS AS IDENTITY generates sequential integers for a column. There are no inserts or updates allowed for this column. Let’s see it in action in the example below.

CREATE TABLE Employees (
EmpId INTEGER GENERATED ALWAYS AS IDENTITY,
EmpName VARCHAR(50)
);

When inserting a value into the EmpName column, the EmpId column value is generated automatically:

INSERT INTO Employees(EmpName)
VALUES
(‘Mary George’), (‘Steven Clint’);

SELECT * FROM Employees;

EmpId	EmpName
1	Mary George
2	Steven Clint

But if you try to manually insert or update a value for the EmpId column, an error message pops up.

GENERATED BY DEFAULT AS IDENTITY

The SQL standard GENERATED BY DEFAULT AS IDENTITY is analogous to the previously-explained GENERATED ALWAYS AS IDENTITY. The difference between them is that we can manually insert or update the value of a column that uses GENERATE BY DEFAULT AS IDENTITY.

To learn more about the SQL standards discussed above and other data types available in SQL, visit our course on Data Types in SQL.

Now You Know SQL Auto-Increments!

To summarize, I’ll just say that SQL’s auto-increment feature is very powerful. It emphasizes the importance of automation in the data science world.

The auto-increment feature has different implementations in different databases, but the result is always the same, i.e. we get automatically-generated values for the numeric columns. I’m sure it’s something you’ll need more than once as you design a database.