SQL 89 🛢️ Sequences and Auto-Increment Mechanisms - KandZ

⏱️ Estimated read: 14 min | |

Every row needs an identifier. The identifier must be unique, it must be generated without a round trip to the application, and it must work when two transactions insert at the same time. The database provides the mechanism: a counter that produces the next value on demand. The SQL standard calls it a sequence, the SQL Server calls it an identity column, the MySQL calls it AUTO_INCREMENT, and the SQLite calls it AUTOINCREMENT. The names differ, the semantics differ, and the choice between them affects the portability, the concurrency, and the gap behavior. This chapter covers the four mechanisms, the ways they generate the values, and the patterns that make the identifiers predictable.

Key point: A sequence is a database object, and a column is a table attribute. A DEFAULT nextval('seq') ties the column to the sequence, and the sequence can be shared across the tables. An IDENTITY column and an AUTO_INCREMENT column are tied to the one table, and the value is generated only for that column.

Why the identity mechanisms exist

The concurrency problem. The application cannot read the maximum ID, add one, and insert. Two transactions do that at the same time, and both get the same value. The database must generate the value atomically, and the mechanisms are the atomic counters.

The gap problem. The counter must not reuse a value. If a transaction rolls back, the value it consumed is gone, and the next insert gets the next value. The gap is the price of the concurrency, and the mechanism guarantees the uniqueness.

The portability problem. The standard SEQUENCE is portable across the PostgreSQL, the Oracle, and the SQL Server (2012+). The IDENTITY and the AUTO_INCREMENT are vendor-specific. The standard form is the portable, and the vendor form is the convenient.

The performance problem. The sequence is a single object, and the increment is the fast operation. The contention appears when the many sessions request the values at the same time, and the cache and the increment options reduce the contention.

The distribution problem. The global sequence produces the monotonic values, and the distributed systems use the different strategies: the UUID, the Snowflake ID, the segment allocation. The mechanism is the local optimization, and the distribution needs the additional design.

The security problem. The sequential ID reveals the row count and the insertion order. The attacker can enumerate the records and infer the business volume. The UUID is the alternative, and the sequence remains the internal key.

The migration problem. The sequence can be reset, the value can be set, and the ownership can be changed. The ALTER SEQUENCE and the setval are the tools, and the migration scripts use them.

a. The sequence

A sequence is a database object that produces the numeric values in order. The CREATE SEQUENCE defines it, and the nextval reads the next value.

The basic sequence

CREATE SEQUENCE order_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE TABLE orders (
    id BIGINT NOT NULL DEFAULT nextval('order_id_seq'),
    customer_id INT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER SEQUENCE order_id_seq OWNED BY orders.id;

The nextval('order_id_seq') generates the value, and the OWNED BY ties the sequence to the column. The sequence is dropped when the table is dropped.

The sequence options

Option	Purpose
`START WITH`	The first value
`INCREMENT BY`	The step
`MINVALUE`	The minimum
`MAXVALUE`	The maximum
`CACHE`	The number to pre-allocate
`CYCLE`	The wrap-around
`OWNED BY`	The column ownership

The nextval and the currval

SELECT nextval('order_id_seq');  -- the next value
SELECT currval('order_id_seq');  -- the last value in this session
SELECT lastval();                -- the last value in this session

The nextval increments the sequence and returns the value. The currval returns the last value the session obtained, and it errors if the session has not called the nextval.

The SERIAL shorthand

PostgreSQL provides the SERIAL and the BIGSERIAL shorthands. The shorthand creates the sequence and the default in one step.

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id INT NOT NULL
);

The BIGSERIAL expands to the BIGINT, the sequence, and the DEFAULT nextval(...). The sequence name is the <table>_<column>_seq.

The setval and the reset

SELECT setval('order_id_seq', 1000);
SELECT setval('order_id_seq', 1000, false);  -- the next value is 1

The setval sets the current value. The third argument is the is_called flag: the true means the next nextval returns the 1001, and the false means the 1000.

The ALTER SEQUENCE

ALTER SEQUENCE order_id_seq RESTART WITH 1000;
ALTER SEQUENCE order_id_seq INCREMENT BY 10;
ALTER SEQUENCE order_id_seq CACHE 100;

The RESTART WITH resets the sequence, and the INCREMENT BY changes the step.

The sequence table

Database	Syntax
PostgreSQL	`CREATE SEQUENCE seq;`
Oracle	`CREATE SEQUENCE seq;`
SQL Server	`CREATE SEQUENCE seq;`
MySQL	Not supported

b. The identity column

The identity column is the column attribute that generates the value. The SQL Server has the IDENTITY, and the PostgreSQL 10+ has the GENERATED ... AS IDENTITY.

The SQL Server identity

CREATE TABLE orders (
    id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT NOT NULL
);

The IDENTITY(1,1) means the seed 1 and the increment 1.

The identity functions

SELECT SCOPE_IDENTITY();  -- the last identity in this scope
SELECT @@IDENTITY;        -- the last identity in this session
SELECT IDENT_CURRENT('orders');  -- the last identity for the table

The SCOPE_IDENTITY is the safe one. The @@IDENTITY is affected by the triggers.

The PostgreSQL identity

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL
);

The GENERATED ALWAYS rejects the explicit value, and the GENERATED BY DEFAULT accepts it.

The identity options

CREATE TABLE orders (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY
        (START WITH 1000 INCREMENT BY 1 CACHE 1)
        PRIMARY KEY,
    customer_id INT NOT NULL
);

The identity accepts the same options as the sequence.

The ALTER TABLE identity

ALTER TABLE orders ALTER COLUMN id RESTART WITH 1000;
ALTER TABLE orders ALTER COLUMN id SET INCREMENT BY 10;

The ALTER TABLE manages the identity column.

The SQL Server identity

CREATE TABLE orders (
    id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT NOT NULL
);

-- The insert without the identity.
INSERT INTO orders (customer_id) VALUES (42);

-- The explicit value requires the IDENTITY_INSERT.
SET IDENTITY_INSERT orders ON;
INSERT INTO orders (id, customer_id) VALUES (1000, 42);
SET IDENTITY_INSERT orders OFF;

The SET IDENTITY_INSERT allows the explicit value.

The identity table

Database	Syntax
PostgreSQL 10+	`GENERATED ALWAYS AS IDENTITY`
SQL Server	`IDENTITY(seed, increment)`
Oracle 12c+	`GENERATED AS IDENTITY`
MySQL	The `AUTO_INCREMENT`

c. The AUTO_INCREMENT and the AUTOINCREMENT

The MySQL and the SQLite have their own mechanisms.

The MySQL AUTO_INCREMENT

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL
);

The AUTO_INCREMENT column is the key, and the database generates the value.

The MySQL last insert id

INSERT INTO orders (customer_id) VALUES (42);
SELECT LAST_INSERT_ID();

The LAST_INSERT_ID() returns the value generated for the last insert.

The MySQL AUTO_INCREMENT settings

SHOW VARIABLES LIKE 'auto_increment%';

SET @@auto_increment_increment = 2;
SET @@auto_increment_offset = 1;

The auto_increment_increment and the auto_increment_offset control the sequence. The settings are used in the multi-master replication to avoid the collisions.

The SQLite AUTOINCREMENT

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INT NOT NULL
);

The AUTOINCREMENT keyword prevents the reuse of the value. The INTEGER PRIMARY KEY without the keyword uses the rowid, and the value can be reused after the delete.

The SQLite last insert rowid

INSERT INTO orders (customer_id) VALUES (42);
SELECT last_insert_rowid();

The last_insert_rowid() returns the rowid of the last insert.

The SQLite rowid

The INTEGER PRIMARY KEY column is the alias for the rowid. The rowid is the internal identifier, and the value is the 64-bit integer.

The MySQL versus the SQLite table

Aspect	MySQL	SQLite
Syntax	`AUTO_INCREMENT`	`AUTOINCREMENT`
The last ID	`LAST_INSERT_ID()`	`last_insert_rowid()`
The reuse	The value is not reused	The value is reused without the keyword
The limit	The column type	The 64-bit

Complete Example Session

-- ============================================
-- PART 1: THE BASIC SEQUENCE
-- ============================================

CREATE SEQUENCE order_id_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE orders (
    id BIGINT NOT NULL DEFAULT nextval('order_id_seq'),
    customer_id INT NOT NULL
);

ALTER SEQUENCE order_id_seq OWNED BY orders.id;

-- ============================================
-- PART 2: THE SERIAL SHORTHAND
-- ============================================

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id INT NOT NULL
);

-- ============================================
-- PART 3: THE NEXTVAL AND THE CURRVAL
-- ============================================

SELECT nextval('order_id_seq');  -- the next value
SELECT currval('order_id_seq');  -- the last value in this session
SELECT lastval();

-- ============================================
-- PART 4: THE SETVAL
-- ============================================

SELECT setval('order_id_seq', 1000);
SELECT setval('order_id_seq', 1000, false);

-- ============================================
-- PART 5: THE IDENTITY COLUMN
-- ============================================

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL
);

-- ============================================
-- PART 6: THE GENERATED BY DEFAULT
-- ============================================

CREATE TABLE orders (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL
);

INSERT INTO orders (id, customer_id) VALUES (1000, 42);  -- the explicit value

-- ============================================
-- PART 7: THE SQL SERVER IDENTITY
-- ============================================

CREATE TABLE orders (
    id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT NOT NULL
);

SELECT SCOPE_IDENTITY();

SET IDENTITY_INSERT orders ON;
INSERT INTO orders (id, customer_id) VALUES (1000, 42);
SET IDENTITY_INSERT orders OFF;

-- ============================================
-- PART 8: THE MYSQL AUTO_INCREMENT
-- ============================================

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL
);

INSERT INTO orders (customer_id) VALUES (42);
SELECT LAST_INSERT_ID();

-- ============================================
-- PART 9: THE SQLITE AUTOINCREMENT
-- ============================================

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INT NOT NULL
);

INSERT INTO orders (customer_id) VALUES (42);
SELECT last_insert_rowid();

-- ============================================
-- PART 10: THE COMPLETE PATTERN
-- ============================================

-- 1. Use the SEQUENCE for the portability.
-- 2. Use the SERIAL/BIGSERIAL for the PostgreSQL shorthand.
-- 3. Use the GENERATED AS IDENTITY for the standard.
-- 4. Use the IDENTITY for the SQL Server.
-- 5. Use the AUTO_INCREMENT for the MySQL.
-- 6. Use the AUTOINCREMENT for the SQLite.
-- 7. Use the nextval/currval for the sequence.
-- 8. Use the SCOPE_IDENTITY for the SQL Server.
-- 9. Use the LAST_INSERT_ID for the MySQL.
-- 10. The gap is the price of the concurrency.

The ten parts showed the basic sequence, the SERIAL shorthand, the nextval and the currval, the setval, the identity column, the GENERATED BY DEFAULT, the SQL Server identity, the MySQL AUTO_INCREMENT, the SQLite AUTOINCREMENT, and the complete pattern.

Quick Reference

The Mechanisms

Database	Syntax
PostgreSQL	`BIGSERIAL` / `GENERATED AS IDENTITY`
SQL Server	`IDENTITY(seed, increment)`
MySQL	`AUTO_INCREMENT`
SQLite	`AUTOINCREMENT`
Oracle	`GENERATED AS IDENTITY`

The Last ID Functions

Database	Function
PostgreSQL	`currval('seq')` / `lastval()`
SQL Server	`SCOPE_IDENTITY()`
MySQL	`LAST_INSERT_ID()`
SQLite	`last_insert_rowid()`

The Sequence Options

Option	Purpose
`START WITH`	The first value
`INCREMENT BY`	The step
`MINVALUE`	The minimum
`MAXVALUE`	The maximum
`CACHE`	The pre-allocate
`CYCLE`	The wrap-around
`OWNED BY`	The column ownership

The Identity Options

Option	Purpose
`ALWAYS`	The explicit value rejected
`BY DEFAULT`	The explicit value accepted
`START WITH`	The first value
`INCREMENT BY`	The step
`CACHE`	The pre-allocate

The Gap Behavior

Mechanism	Gap on rollback
The sequence	Yes
The identity	Yes
The `AUTO_INCREMENT`	Yes
The `AUTOINCREMENT`	Yes

Best Practices

✅ Do This:

-- Use the SEQUENCE for the portability.
CREATE SEQUENCE order_id_seq;  -- ✅

-- Use the GENERATED AS IDENTITY for the standard.
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY  -- ✅

-- Use the BIGSERIAL for the PostgreSQL shorthand.
id BIGSERIAL PRIMARY KEY  -- ✅

-- Use the SCOPE_IDENTITY for the SQL Server.
SELECT SCOPE_IDENTITY();  -- ✅

-- Use the LAST_INSERT_ID for the MySQL.
SELECT LAST_INSERT_ID();  -- ✅

-- Use the OWNED BY to tie the sequence to the column.
ALTER SEQUENCE order_id_seq OWNED BY orders.id;  -- ✅

-- Use the CACHE for the high-throughput.
CREATE SEQUENCE seq CACHE 100;  -- ✅

❌ Don’t Do This:

-- Don't generate the ID in the application.
SELECT MAX(id) + 1 FROM orders;  -- ❌ The race condition

-- Don't use the @@IDENTITY in the SQL Server.
SELECT @@IDENTITY;  -- ❌ The trigger affects it

-- Don't reuse the value after the rollback.
-- The mechanism does not support it.  -- ❌

-- Don't forget the OWNED BY.
CREATE SEQUENCE seq;  -- ⚠️ The sequence outlives the table

-- Don't use the sequential ID as the public identifier.
-- The enumeration risk.  -- ❌

-- Don't use the AUTO_INCREMENT for the distributed system.
-- The collision risk.  -- ❌

Common Pitfalls

Pitfall	Why It Happens	Fix
The duplicate ID	The `MAX + 1`	Use the sequence
The gap in the ID	The rollback	The gap is expected
The wrong last ID	The `@@IDENTITY`	Use `SCOPE_IDENTITY()`
The sequence orphaned	No `OWNED BY`	Add the ownership
The contention	The low cache	Increase the cache
The enumeration	The sequential ID	Use the UUID for the public
The distributed collision	The `AUTO_INCREMENT`	Use the UUID or the Snowflake

Real-World Examples

1. The Sequence

CREATE SEQUENCE order_id_seq START WITH 1;

2. The SERIAL

id BIGSERIAL PRIMARY KEY

3. The Identity

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

4. The SQL Server Identity

id INT IDENTITY(1,1) PRIMARY KEY

5. The MySQL Auto Increment

id INT AUTO_INCREMENT PRIMARY KEY

6. The SQLite Autoincrement

id INTEGER PRIMARY KEY AUTOINCREMENT

7. The nextval

SELECT nextval('order_id_seq');

8. The SCOPE_IDENTITY

SELECT SCOPE_IDENTITY();

9. The LAST_INSERT_ID

SELECT LAST_INSERT_ID();

10. The Complete Pattern

CREATE TABLE orders (id BIGSERIAL PRIMARY KEY, ...);

Visual

The Sequence

┌────────────────────────────────────────────────────────────┐
│  THE SEQUENCE                                              │
│                                                            │
│  CREATE SEQUENCE order_id_seq START WITH 1 INCREMENT BY 1; │
│                                                            │
│  ┌──────────────────────────────────────────────────┐     │
│  │  The counter: 1, 2, 3, 4, 5, ...                  │     │
│  │                                                   │     │
│  │  nextval('order_id_seq')  →  the next value       │     │
│  │  currval('order_id_seq')  →  the last in session  │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  The sequence is the object, and the DEFAULT nextval ties   │
│  the column to it.                                         │
└────────────────────────────────────────────────────────────┘

The Identity

┌────────────────────────────────────────────────────────────┐
│  THE IDENTITY                                              │
│                                                            │
│  CREATE TABLE orders (                                     │
│    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,     │
│    ...                                                     │
│  );                                                        │
│                                                            │
│  ┌──────────────────────────────────────────────────┐     │
│  │  The identity is the column attribute.            │     │
│  │  The value is generated for the column.           │     │
│  │                                                   │     │
│  │  ALWAYS: the explicit value is rejected            │     │
│  │  BY DEFAULT: the explicit value is accepted        │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  The identity is the table-scoped, and the sequence is     │
│  the shared object.                                        │
└────────────────────────────────────────────────────────────┘

The Last ID

┌────────────────────────────────────────────────────────────┐
│  THE LAST ID                                               │
│                                                            │
│  PostgreSQL                                                │
│  ┌──────────────────────────────────────────────────┐     │
│  │  INSERT INTO orders ...;                           │     │
│  │  SELECT currval('order_id_seq');                   │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  SQL Server                                                │
│  ┌──────────────────────────────────────────────────┐     │
│  │  INSERT INTO orders ...;                           │     │
│  │  SELECT SCOPE_IDENTITY();                          │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  MySQL                                                     │
│  ┌──────────────────────────────────────────────────┐     │
│  │  INSERT INTO orders ...;                           │     │
│  │  SELECT LAST_INSERT_ID();                          │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  SQLite                                                    │
│  ┌──────────────────────────────────────────────────┐     │
│  │  INSERT INTO orders ...;                           │     │
│  │  SELECT last_insert_rowid();                       │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

The Gap

┌────────────────────────────────────────────────────────────┐
│  THE GAP                                                   │
│                                                            │
│  BEGIN;                                                    │
│    INSERT INTO orders (customer_id) VALUES (1);            │
│    -- The id is 42                                         │
│    ROLLBACK;                                               │
│                                                            │
│  BEGIN;                                                    │
│    INSERT INTO orders (customer_id) VALUES (2);            │
│    -- The id is 43, not 42                                 │
│    COMMIT;                                                 │
│                                                            │
│  ┌──────────────────────────────────────────────────┐     │
│  │  The value 42 is consumed by the rollback.        │     │
│  │  The gap is the price of the concurrency.         │     │
│  │  The uniqueness is guaranteed, the sequence is not.│     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

Summary

Item	Value
Sequence	The database object producing the values
`nextval`	The next value
`currval`	The last value in the session
`SERIAL`	The PostgreSQL shorthand
`IDENTITY`	The SQL Server column attribute
`AUTO_INCREMENT`	The MySQL column attribute
`AUTOINCREMENT`	The SQLite keyword
`SCOPE_IDENTITY`	The SQL Server last ID
`LAST_INSERT_ID`	The MySQL last ID
The gap	The price of the concurrency

Key takeaways:

A sequence is a database object, and the column is tied to it with the DEFAULT nextval(...). The sequence can be shared across the tables, and the OWNED BY ties the lifetime to the column.
The SERIAL and the BIGSERIAL are the PostgreSQL shorthands. They create the sequence and the default in one step. The GENERATED AS IDENTITY is the standard form, and the GENERATED ALWAYS rejects the explicit value.
The IDENTITY is the SQL Server column attribute. The IDENTITY(seed, increment) sets the start and the step. The SCOPE_IDENTITY() is the safe last ID function.
The AUTO_INCREMENT is the MySQL column attribute. The LAST_INSERT_ID() returns the value. The auto_increment_increment and the auto_increment_offset control the sequence for the replication.
The AUTOINCREMENT is the SQLite keyword. It prevents the value reuse. The INTEGER PRIMARY KEY without the keyword uses the rowid, and the value can be reused.
The gap is the price of the concurrency. The rollback consumes the value, and the next insert gets the next value. The uniqueness is guaranteed, and the sequential is not.
The sequential ID is not the public identifier. The enumeration risk is the security concern, and the UUID is the alternative for the public key.
The MAX(id) + 1 is the race condition. The application-level generation is the bug, and the database mechanism is the fix.

Remember: The sequences and the auto-increment mechanisms are the database’s way of generating the unique identifiers. The sequence is the standard object, the IDENTITY is the SQL Server attribute, the AUTO_INCREMENT is the MySQL attribute, and the AUTOINCREMENT is the SQLite keyword. The mechanism guarantees the uniqueness, and the gap is the expected behavior. The result is the identifier that works under the concurrency, and the application that does not need the round trip to generate the value.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!