sql_autoincrement

SQL Auto-Incrementing Fields: Generating Unique IDs

Auto-increment (also known as identity or sequence) allows the database to automatically generate a unique number when a new record is inserted into a table. This is commonly used for primary key columns.

Why Use Auto-Increment Fields?

Often, we need a primary key that uniquely identifies each row but doesn’t carry any business meaning (a surrogate key). Auto-increment fields are perfect for this because:

• They guarantee uniqueness.
• They simplify data insertion, as you don’t need to manually generate and provide the ID.
• They are efficient and typically numeric, which is good for indexing and joins.

The exact syntax for creating auto-increment fields varies between database systems.

Syntax for MySQL: AUTO_INCREMENT

MySQL uses the AUTO_INCREMENT keyword.

CREATE TABLE Persons (
  ID INT NOT NULL AUTO_INCREMENT,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Address VARCHAR(255),
  City VARCHAR(255),
  PRIMARY KEY (ID)
);

By default, AUTO_INCREMENT starts at 1 and increments by 1. To set a different starting value: ALTER TABLE Persons AUTO_INCREMENT = 100;

Inserting a new record (ID is generated automatically):

INSERT INTO Persons (FirstName, LastName)
VALUES ('Lars', 'Monsen');

Syntax for SQL Server: IDENTITY

SQL Server uses the IDENTITY keyword.

CREATE TABLE Persons (
  ID INT IDENTITY(1,1) PRIMARY KEY,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Address VARCHAR(255),
  City VARCHAR(255)
);

IDENTITY(seed, increment): seed is the starting value, increment is the step. IDENTITY(1,1) starts at 1 and increments by 1. IDENTITY(10,5) would start at 10 and increment by 5.

Inserting a new record:

INSERT INTO Persons (FirstName, LastName)
VALUES ('Lars', 'Monsen');

Syntax for PostgreSQL: SERIAL or GENERATED AS IDENTITY

PostgreSQL offers several ways. The SERIAL pseudo-type is a traditional shorthand:

CREATE TABLE Persons (
  ID SERIAL PRIMARY KEY, -- Creates an integer column and an associated sequence
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Address VARCHAR(255),
  City VARCHAR(255)
);

The modern, SQL standard way (PostgreSQL 10+) is GENERATED AS IDENTITY:

CREATE TABLE Persons (
  ID INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, -- Or GENERATED BY DEFAULT AS IDENTITY
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Address VARCHAR(255),
  City VARCHAR(255)
);

GENERATED ALWAYS AS IDENTITY: The system always generates the value. You cannot insert your own. GENERATED BY DEFAULT AS IDENTITY: The system generates a value if one is not supplied. You can override it.

Inserting a new record:

INSERT INTO Persons (FirstName, LastName)
VALUES ('Lars', 'Monsen');

Syntax for Oracle: IDENTITY Columns (12c+) or SEQUENCE

Oracle 12c and later support SQL standard IDENTITY columns:

CREATE TABLE Persons (
  ID NUMBER GENERATED AS IDENTITY PRIMARY KEY,
  LastName VARCHAR2(255) NOT NULL,
  FirstName VARCHAR2(255),
  Address VARCHAR2(255),
  City VARCHAR2(255)
);

For older Oracle versions, or for more control, you use a SEQUENCE object explicitly:

CREATE SEQUENCE person_id_seq
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 20; -- Caching improves performance

Then, use sequence_name.NEXTVAL when inserting:

INSERT INTO Persons (ID, FirstName, LastName)
VALUES (person_id_seq.NEXTVAL, 'Lars', 'Monsen');

Syntax for SQLite: AUTOINCREMENT

SQLite uses INTEGER PRIMARY KEY with an optional AUTOINCREMENT keyword. If AUTOINCREMENT is used, it ensures that IDs are strictly increasing and never reused. Without it, IDs are typically reused if higher IDs are deleted.

CREATE TABLE Persons (
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  LastName TEXT NOT NULL,
  FirstName TEXT,
  Address TEXT,
  City TEXT
);

Inserting a new record:

INSERT INTO Persons (FirstName, LastName)
VALUES ('Lars', 'Monsen');

Practical Application: Almost every table that represents entities (like Users, Products, Orders) will benefit from an auto-incrementing primary key for stable and efficient referencing.