Auto-Increment / Identity

Most tables use a surrogate primary key — a meaningless integer whose only job is to identify each row. Rather than requiring every INSERT statement to generate and supply that value manually, databases offer an auto-incrementing column type that assigns the next sequential integer automatically. The exact syntax for this differs across database systems, even though the underlying idea is identical everywhere.

Dialect comparison

Database	Syntax	Notes
PostgreSQL	SERIAL / BIGSERIAL, or GENERATED ALWAYS AS IDENTITY	GENERATED AS IDENTITY (SQL-standard) is the modern, recommended form; SERIAL is older shorthand built on a sequence.
MySQL / MariaDB	AUTO_INCREMENT	Declared directly on an integer column; only one AUTO_INCREMENT column allowed per table, and it must be indexed (usually as the primary key).
SQL Server	IDENTITY(seed, increment)	E.g. IDENTITY(1,1) starts at 1 and increases by 1 for each new row.
SQLite	INTEGER PRIMARY KEY AUTOINCREMENT	A plain INTEGER PRIMARY KEY already auto-increments by default in SQLite; AUTOINCREMENT adds stricter guarantees against reusing old values.
Oracle	GENERATED ALWAYS AS IDENTITY	Oracle 12c and later support the SQL-standard IDENTITY syntax directly.

Worked CREATE TABLE examples

SQL

-- PostgreSQL
CREATE TABLE customers_pg (
  customer_id  INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name         VARCHAR(100) NOT NULL
);

-- MySQL / MariaDB
CREATE TABLE customers_mysql (
  customer_id  INT AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(100) NOT NULL
);

-- SQL Server
CREATE TABLE customers_mssql (
  customer_id  INT IDENTITY(1,1) PRIMARY KEY,
  name         VARCHAR(100) NOT NULL
);

-- SQLite
CREATE TABLE customers_sqlite (
  customer_id  INTEGER PRIMARY KEY AUTOINCREMENT,
  name         TEXT NOT NULL
);

With any of these in place, an INSERT simply omits the identity column, and the database fills it in automatically:

SQL

INSERT INTO customers_pg (name) VALUES ('Amara Okafor');
INSERT INTO customers_pg (name) VALUES ('Diego Ruiz');

customer_id | name
------------+---------------
1           | Amara Okafor
2           | Diego Ruiz

Retrieving the generated value

After inserting a row, application code often needs to know the id that was just generated (to insert related rows, or to return it to the caller). Each database exposes its own way to retrieve it:

PostgreSQL — INSERT ... RETURNING customer_id.
MySQL — the LAST_INSERT_ID() function, or the driver's insertId property.
SQL Server — SCOPE_IDENTITY() immediately after the insert.
SQLite — the last_insert_rowid() function, or the driver's lastID property.

Note

Auto-incrementing values are not guaranteed to be perfectly contiguous. A failed transaction, an explicit ROLLBACK, or a server restart can consume a number from the sequence without a row ever actually being committed, leaving a permanent gap (e.g. ids jump from 41 straight to 45). This is normal and expected — auto-increment columns guarantee uniqueness and increasing order, not a gap-free count of rows.

Warning

Never rely on auto-increment values being sequential with no gaps for business logic (for example, don't assume "order count = highest order_id"). If a true row count is needed, use COUNT(*) instead of reading the current identity value.

Tip

The SQL-standard GENERATED ALWAYS AS IDENTITY syntax (supported by PostgreSQL, Oracle, and others) is generally preferred over older vendor-specific shorthands like SERIAL, since it is portable across any database that implements the standard and makes the intent — an automatically generated identity value — explicit in the column definition.