How to Navigate Auto-Increment Columns in Relational Databases

Auto-increment is a feature in databases that automatically generates a unique number for each new row added to a table. This number is usually used as a primary key to uniquely identify each row in the table.

The database system automatically assigns the next available number to each new row, saving the user from having to manually manage the primary key values. This simplifies the process of adding new rows and ensures that each row has a unique identifier.

Below we explore how tables are created with an auto-increment column in different RDBMS

SQLite

CREATE TABLE example_table (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name VARCHAR(50),
   age INT
);

In this example, the id column is defined as an integer data type with the AUTOINCREMENT keyword. It is also set as the primary key of the table.

MySQL and MariaDB

CREATE TABLE example_table (
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(50),
   age INT
);

In this example, the id column is defined as an auto-increment column with the AUTO_INCREMENT keyword. It is also set as the primary key of the table.

SQL Server

CREATE TABLE example_table (
   id INT IDENTITY(1,1) PRIMARY KEY,
   name VARCHAR(50),
   age INT
);

In this example, the id column is defined as an identity column with the IDENTITY keyword. It is also set as the primary key of the table. The numbers 1,1 specify the starting value and increment value of an auto-incrementing column. For example, we can start numbering with 100 and add 10 (100, 110, 120, 130...) to each next row by replacing IDENTITY(1,1) with IDENTITY(100, 10). Moreover, negative numbers can be used as a sequence start and step. Try it yourself here

Oracle

CREATE SEQUENCE example_table_seq;

CREATE TABLE example_table (
   id NUMBER DEFAULT example_table_seq.nextval PRIMARY KEY,
   name VARCHAR2(50),
   age NUMBER
);

In this example, a sequence is created first with the CREATE SEQUENCE statement. Then the id column is defined as a number data type with a default value of the next value of the sequence. It is also set as the primary key of the table.

The sequence example_table_seq started with 1 and step 1. When we need to customize it we can use INCREMENT BY and START WITH parameters:

CREATE SEQUENCE example_table_seq 
    INCREMENT BY 10
    START WITH 10;

PostgreSQL

PostgreSQL has several options for defining the auto-increment column. The most popular is SERIAL keyword

CREATE TABLE example_table (
   id SERIAL PRIMARY KEY,
   name VARCHAR(50),
   age INT
);

In this example, the id column is defined as a serial data type, which is equivalent to an auto-increment column in other DBMS. It is also set as the primary key of the table.

Also, you can use both IDENTITY and SEQUENCE syntax in PostgreSQL to create an auto-increment column.

Here are the examples of creating an auto-increment column using IDENTITY and SEQUENCE syntax in PostgreSQL

CREATE TABLE example_table (
   id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name VARCHAR(50),
   age INT
);

CREATE SEQUENCE example_table_seq;

CREATE TABLE example_table (
   id INT DEFAULT nextval('example_table_seq') PRIMARY KEY,
   name VARCHAR(50),
   age INT
);

To insert a new row into the table with an auto-increment column, we can use the INSERT INTO statement, specifying all columns except auto-increment:

INSERT INTO example_table (name, age) VALUES ('John Smith', 30);

Most SQL dialects allow us to insert several rows in a single query.

INSERT INTO example_table (name, age)
VALUES
  ('Jane Doe', 25),
  ('Bob Johnson', 40),
  ('Alice Brown', 35);

SQLite, MySQL & MariaDB allows us to insert arbitrary values into the auto-increment column and update the sequence.

INSERT INTO example_table (id, name, age) VALUES (11, 'John Smith', 30);

INSERT INTO example_table (name, age)
VALUES
  ('Jane Doe', 25),
  ('Bob Johnson', 40),
  ('Alice Brown', 35);

SELECT * FROM example_table;

In the above example, we insert 'John Smith' with id = 11 and the next records inserts uses next sequence values 12, 13, 14.

+====+=============+=====+
| id | name        | age |
+====+=============+=====+
| 11 | John Smith  | 30  |
| 12 | Jane Doe    | 25  |
| 13 | Bob Johnson | 40  |
| 14 | Alice Brown | 35  |
+----+-------------+-----+

MS SQL Server does not allow such voluntarism by default. So if we need to insert a value in the identity column we must set IDENTITY_INSERT on our table:

SET IDENTITY_INSERT example_table ON
INSERT INTO example_table (id, name, age) VALUES (11, 'John Smith', 30)
SET IDENTITY_INSERT example_table OFF;

The above query inserts the record with id = 11 and moves up the identity counter for the next records.

What about Oracle?

It allows us to insert values in the id column, without the sequence being affected. So, we can insert a row with an arbitrary id, but this can cause a collision like in the next example:

CREATE SEQUENCE example_table_seq;

CREATE TABLE example_table (
   id NUMBER DEFAULT example_table_seq.nextval PRIMARY KEY,
   name VARCHAR2(50),
   age NUMBER
);
-- insert row with id = 3
INSERT INTO example_table (id, name, age) VALUES (3, 'John Smith', 30);

-- next to queries will add records with id 1 and 2
INSERT INTO example_table (name, age) VALUES ('Jane Doe', 25); -- 1
INSERT INTO example_table (name, age) VALUES ('Bob Johns', 40); -- 2

-- below query cause to error OCIStmtExecute: ORA-00001: unique constraint (0c7690dacb6b.SYS_C009760) violated 
INSERT INTO example_table (name, age) VALUES ('Alice Brown', 35); 

SELECT * FROM example_table;

PostgreSQL with SERIAL or SEQUENCE syntax for the auto-increment column shows us the same behavior as Oracle (row inserted, sequence not updated, error on duplicate). But, when we use IDENTITY syntax insert row with an arbitrary id it is not allowed.

This restriction can be forced by OVERRIDING SYSTEM VALUE like in the next example, but again it is not updating the sequence and collision may ensue.

CREATE TABLE example_table (
   id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name VARCHAR(50),
   age INT
);

INSERT INTO example_table (id, name, age) OVERRIDING SYSTEM VALUE VALUES (3, 'John Smith', 30);

INSERT INTO example_table (name, age) VALUES ('Jane Doe', 25);
INSERT INTO example_table (name, age) VALUES ('Bob Johnson', 40);
INSERT INTO example_table (name, age) VALUES ('Alice Brown', 35);

SELECT * FROM example_table;

Let’s move on!

How to change an auto-increment value

In SQLite, you can reset the auto-increment value for a table by using the sqlite_sequence table. The sqlite_sequence table is an internal table used by SQLite to keep track of the next auto-increment value for each table in a database.

Each row in the sqlite_sequence table represents a table in the database, and the seq column stores the next auto-increment value for that table.

So when we need to change it just try next:

UPDATE sqlite_sequence SET seq = 100 WHERE name = 'example_table';

INSERT INTO example_table (name, age) VALUES ('John Gold', 30);

The above code will move the auto-increment value to 100 and the next inserted row will get id = 101;

We can delete auto-increment with next query:

DELETE FROM sqlite_sequence WHERE name = 'example_table';

or set it as less than the maximal value in the auto-incremented column but after the next row, the inserted sqlite_sequence will be fixed with the right value.

Just look next code snippet:

CREATE TABLE example_table (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name VARCHAR(50),
   age INT
);

INSERT INTO example_table (name, age)
VALUES
  ('Jane Doe', 25),
  ('Bob Johnson', 40),
  ('Alice Brown', 35);

SELECT * FROM sqlite_sequence WHERE name = 'example_table';

After the table is created and filled we see:

+===============+=====+
| name          | seq |
+===============+=====+
| example_table | 3   |
+---------------+-----+

We can drop it and make sure that value disappeared

DELETE FROM sqlite_sequence WHERE name = 'example_table';

SELECT * FROM sqlite_sequence WHERE name = 'example_table';

After the next record of the inserted value, the sequence will be restored as well. Try it here

MySQL & MariaDB also allows changing of the auto-increment using ALTER TABLE query but it can not be less than the maximal value in the auto-incremented column:

ALTER TABLE example_table AUTO_INCREMENT = 100;

In Oracle, it is possible to alter a sequence using the ALTER SEQUENCE statement. This statement allows you to change the characteristics of a sequence, such as its minimum and maximum values, its increment, and its starting value. If you need to make more significant changes to a sequence, such as changing its data type or dropping it entirely, then you may need to drop and re-create the sequence.

DROP SEQUENCE example_table_seq;
CREATE SEQUENCE example_table_seq INCREMENT BY 1 START WITH 100;

Note that in Oracle, there is no conflict checking between the new sequence value and existing records. Therefore, it is important to exercise caution and use this feature responsibly to avoid unintended consequences.

In PostgreSQL, it doesn't matter how your auto-increment column is created (SERIAL, GENERATED ALWAYS AS IDENTITY, or using CREATE SEQUENCE) because you can use the ALTER SEQUENCE statement to modify the sequence. However, this DBMS does not guarantee the absence of conflicts after the sequence is modified, so it is important to exercise caution.

ALTER SEQUENCE example_table_id_seq RESTART WITH 100;

Conclusion

Auto-increment is a feature in databases that generates a unique number for each new row added to a table, saving the user from manually managing the primary key values. This feature is supported by most RDBMS and can be easily implemented using different syntax in each database. SQLite, MySQL, and MariaDB all support auto-increment columns, as well as PostgreSQL and Oracle, which also allow for more advanced options like sequences and serial data types. Although each database has its own syntax for implementing and managing auto-increment.

Overall, the auto-increment feature greatly simplifies the process of adding new rows and ensures that each row has a unique identifier, which is essential for many database operations.

If you want to practice creating tables with auto-increment columns and executing SQL statements, you can try using an online SQL editor like SQLize.online, which allows you to create tables, insert data, and run SQL queries on a live database.

Also published here.