Back to articles list January 8, 2021 - 11 minutes read What Is Auto-Increment in SQL? Martyna Sławińska Martyna is a software developer with a passion for programming, automation, and innovation. Currently working for the global corporate IT consulting sector, she is a new-generation techie who hails from Warsaw University of Technology, Poland. She has certifications in both database administration and Java development. Her interests include creating small automation devices using embedded systems and various electronic components. In her free time, she practices yoga, which helps her to center her thoughts and come up with new ideas. Tags: sql learn sql data engineering 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: PersonIdentityNumberFirstNameLastNameAge ………… 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; PersonIdentityNumberFirstNameLastNameAge 1BradCooper25 2AmySmith30 3RogerAnderson27 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. ProductIdProductNameQuantityInStock ……… 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; ProductIdProductNameQuantityInStock 1Apple500 2Banana200 3Pear300 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; IdNameAge 1Monkey3 2Dog5 3Cat7 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; IdNameAge 100Monkey3 110Dog5 120Cat7 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; IdName 1January 2February 3March 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; IdName 100First Sequence Number 110Second Sequence Number 120Third Sequence Number 130Fourth 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; IdName 3Order #2345 4Order #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; EmpIdEmpName 1Mary George 2Steven 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. Tags: sql learn sql data engineering You may also like What Is Data Engineering? What’s data engineering and how does it fit in with data science and Big Data? Read this article to find out. Read more What is an SQL View? An SQL view allows you to not only store an SQL query but also use it as a table in other queries. Learn how to apply this helpful SQL feature! Read more What Is a Foreign Key in SQL? Do you know what a SQL foreign key is? Read this article to find out how they work and how they are related to primary keys. Read more What Is a SQL Constraint? This article introduces the concept of SQL constraints. The most common SQL constraints are explained along with their application. Read more Top 7 Online Courses for Data Engineers These 7 online courses for data engineers are designed to set you up for one of the most coveted career paths in today’s world. Read more How to Recognize SQL Text Data Type Learn the characteristics of every SQL text data type. Discover Oracle data type for large text, PostgreSQL fixed-size character text and more. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.