Everything you wanted to know about Auto Increment but were afraid to ask

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 can see how to create table with 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...) 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 have several options for define 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 table with auto-increment column, we can use the INSERT INTO statement, specifying for all columns except auto-increment:

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

Most of SQL dialects allows to insert several rows in single query.

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

SQLite, MySQL & MariaDB allows to insert arbitrary value into 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 above example we inserting 'John Smith' with id = 11 and 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 value in identity column we must to 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 record with id = 11 and move up identity counter for next records.

What about Oracle? It allows to insert values in id column, without sequence be affected, so we can insert row with arbitrary id, but this can cause to collision like in 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 auto-increment column show us same behavior like Oracle (row inserted, sequence not updated, error on duplicate). But when we use IDENTITY syntax insert row with arbitrary id is not allowed. This restriction can be forced by OVERRIDING SYSTEM VALUE like in next example, but again it is not update the sequence and collision may cause.

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;

Lets 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 auto-increment value to 100 and 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 less than maximal value in auto-incremented column but after next row will be inserted the sqlite_sequence will be fixed with 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 table create 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';

but after next record inserted value of sequence will be restored as well. Try it here

MySQL & MariaDB also allows to change auto-increment using ALTER TABLE query but it can not be less than maximal value in 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;

In 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 mange 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.