SQL Auto Increment and Identity: Gaps, Keys, and Safe Retrieval

Generated SQL identifiers: uniqueness, gaps, and retrieval

A generated identifier allocates a value when a row is inserted. It is not a gapless row count, a business invoice sequence, or a reliable commit timestamp. This guide demonstrates PostgreSQL 16 identity columns with a complete rollback fixture, then distinguishes the MySQL 8.4 AUTO_INCREMENT syntax. Keep generated keys stable once other rows or systems reference them.

State the key contract

The id identifies one stored widget and is enforced by a primary key. Name is an attribute, not necessarily a unique business identity. A generated identity column is automatically non-NULL in PostgreSQL, but generation alone does not guarantee uniqueness if values are overridden or the sequence is reset; the PRIMARY KEY remains necessary.

Use an identity column in PostgreSQL

BEGIN;
CREATE TEMP TABLE identity_demo (
 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 name text NOT NULL
) ON COMMIT DROP;
INSERT INTO identity_demo(name) VALUES('kept first') RETURNING id,name;
SAVEPOINT trial;
INSERT INTO identity_demo(name) VALUES('rolled back') RETURNING id,name;
ROLLBACK TO SAVEPOINT trial;
INSERT INTO identity_demo(name) VALUES('kept next') RETURNING id,name;
SELECT id,name,ROW_NUMBER() OVER(ORDER BY id) AS display_row
FROM identity_demo ORDER BY id;
ROLLBACK;

returned insert IDs: 1, then 2, then 3

Final rows before outer rollback:
id | name | display_row
1 | kept first | 1
3 | kept next | 2

Run the entire block in a disposable PostgreSQL session. It creates only a temporary table and rolls back the fixture. The savepoint removes row 2, but its allocated sequence value is not reclaimed. Row 3 therefore receives id 3. The final ROW_NUMBER gives contiguous display positions without changing stored keys.

Retrieve the value from the insert, not MAX(id)

RETURNING associates the generated id with the row inserted by that statement. SELECT MAX(id) after insertion can observe another session’s row and is not a safe generated-key retrieval method. Likewise, MAX(id)+1 is not a concurrency-safe allocator: two sessions can calculate the same next number. Use the database’s allocator and a uniqueness constraint.

GENERATED ALWAYS normally rejects an explicitly supplied non-default identity value. PostgreSQL also offers GENERATED BY DEFAULT and deliberate override mechanisms for controlled imports. Those choices change who owns id assignment; they do not automatically synchronize arbitrary imported values with future generated values. Plan imports and restores with the appropriate documented procedure rather than manually resetting a live sequence while writers continue.

MySQL uses AUTO_INCREMENT, with its own rules

This is a MySQL 8.4/InnoDB example, checked against the official documentation but not executed on a native MySQL server during this revision. Use a disposable connection and a new temporary table name. Its explicit cleanup removes only that temporary table; do not apply the block to an existing application table.

CREATE TEMPORARY TABLE identity_mysql_demo (
 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
INSERT INTO identity_mysql_demo(name) VALUES('example');
SELECT LAST_INSERT_ID() AS inserted_id;
SELECT id,name FROM identity_mysql_demo ORDER BY id;
DROP TEMPORARY TABLE identity_mysql_demo;

For this fresh single-row fixture:
inserted_id = 1
id | name
1 | example

Keep LAST_INSERT_ID() on the same connection as its insert. Multi-row inserts, explicit IDs, SQL modes, storage engines, allocation settings, and error paths have additional rules. Do not generalize this fresh one-row result into a promise that future identifiers are consecutive. PostgreSQL SERIAL is older sequence-backed shorthand; modern identity syntax makes the generation policy explicit. SQL Server uses IDENTITY syntax with its own behavior.

Treat gaps and exhaustion as different issues

Rollbacks, failed or conflicting inserts, and other allocation behavior can leave gaps. Deleting a row does not mean its old id is safe to reuse. An old id may remain in audit records, exports, URLs, caches, or external systems even after local foreign-key references are gone. Do not renumber records just to make a report look tidy.

Monitor capacity for the chosen type and generator settings. BIGINT provides a larger range, but does not make capacity or replication design irrelevant. If a business process truly needs a gapless legal or accounting number, define allocation, cancellation, concurrency, and audit rules separately from the internal row key. An identity column alone does not implement that workflow.

Use evidence when diagnosing a duplicate-key error

First confirm the exact constraint that failed. It may be a unique name or external request key rather than the generated id. Check whether the application supplies IDs, whether a restore/import changed data or sequence state, and whether multiple writers use the intended allocator. Preserve the database and stop guessing resets based on MAX(id). A repair needs exclusive coordination appropriate to the live workload and a tested recovery plan.

The sequence value is not a count of committed rows or a strict chronological order of commits. Concurrent transactions can allocate in one order and commit in another. Store an explicit event or creation time when that fact matters, and define tie-breaking independently.

References and interview checks

Explain why the fixture ends with IDs 1 and 3, why the primary key is still needed, and how the application obtains the inserted ID. References: PostgreSQL identity definition, sequence behavior, INSERT RETURNING, and MySQL AUTO_INCREMENT. For schema design around historical facts, see the tested order-system design.