Sequences and Auto-Generated IDs

Most tables use surrogate keys generated automatically by the database.

Concept

Most tables use surrogate keys generated automatically by the database. The mechanisms are:

Independent database object that generates numbers on request

PostgreSQL, Oracle, SQL Server 2012+, MariaDB 10.3+, Db2 IDENTITY column

Column property that auto-generates values (standard SQL: GENERATED ... AS IDENTITY)

SQL Server IDENTITY(1,1), PostgreSQL 10+, Oracle 12c+, Db2 MySQL/MariaDB column attribute MySQL, MariaDB SERIAL / BIGSERIAL

PostgreSQL shorthand that creates a sequence and a default PostgreSQL (legacy; prefer IDENTITY) ROWID / INTEGER PRIMARY KEY Built-in row identifier

SQLite (AUTOINCREMENT keyword optional) 128-bit globally unique identifier

All (native type in PostgreSQL, SQL Server)

Why It Matters

Surrogate keys must be unique under heavy concurrency without application coordination.

ID choice affects index performance, replication, sharding, security (guessable IDs), and data merging across systems.

Syntax

-- Standard SQL identity (PostgreSQL 10+, Oracle 12c+, Db2)
id BIGINT GENERATED ALWAYS AS IDENTITY          -- database always generates; manual values rejected (unless OVERRIDING SYSTEM VALUE)
id BIGINT GENERATED BY DEFAULT AS IDENTITY      -- generates unless a value is supplied
id BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1000 INCREMENT BY 1)

-- Sequence
CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1 [CACHE 20] [NO CYCLE];

Example

29.1 AUTO_INCREMENT (MySQL) Brake warning light 29.2 IDENTITY (SQL Server)

@@IDENTITY can return an identity generated by a trigger on another table; SCOPE_IDENTITY() or the OUTPUT clause is safer.

29.3 SERIAL and IDENTITY (PostgreSQL) Why IDENTITY over SERIAL: standard syntax, the sequence is tied to the column (permissions and dropping handled together), and GENERATED ALWAYS prevents accidental manual values.

Sequences are useful when:

One number series is shared across several tables (e.g., a global document number).

You need the ID before inserting (to insert parent and children in one batch).

You need custom increments, cycling, or large caches for performance.

Gaps are normal. Sequence and identity values are not rolled back when a transaction aborts, and cached values are lost on restart. Never use them where gapless numbering is legally required (some invoice regulations); implement gapless numbers with a locked counter table instead.

UUID versions: v4 is random; v7 (RFC 9562, 2024) is time-ordered and index-friendly. Random UUIDs as clustered keys cause page splits and fragmentation because inserts land in random positions of the B-tree.

29.6 Advantages and disadvantages Integer IDENTITY / AUTO_INCREMENT

Compact (4–8 bytes), fast, ordered inserts, human-readable Guessable (enumeration risk in URLs), conflicts when merging databases, single-writer generation, reveals volume

Flexible, shareable across tables, pre-fetchable, cacheable Extra object to manage; not available in MySQL UUID v4 (random)

Globally unique, can be generated by clients/offline devices, not guessable, easy merging/sharding

16 bytes (36 as text), random index insertion, harder to read and debug UUID v7 / sequential GUID

Globally unique and time-ordered, index friendly Still 16 bytes; exposes creation time Meaningful, no extra column

May change; may be long; privacy concerns 29.7 Database-specific summary Preferred auto ID Retrieve generated ID GENERATED ALWAYS AS IDENTITY RETURNING id IDENTITY(1,1) or SEQUENCE

OUTPUT INSERTED.id / SCOPE_IDENTITY() GENERATED... AS IDENTITY (12c+) or SEQUENCE RETURNING id INTO:v INTEGER PRIMARY KEY

last_insert_rowid() / RETURNING (3.35+) SQLite note: INTEGER PRIMARY KEY may reuse IDs of deleted rows with the highest value; adding AUTOINCREMENT prevents reuse at a small performance cost.

CREATE TABLE tickets (
    ticket_id  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title      VARCHAR(200) NOT NULL
) AUTO_INCREMENT = 1000;         -- optional starting value

INSERT INTO tickets (title) VALUES ('Brake warning light'), ('Infotainment reboot');
SELECT LAST_INSERT_ID();          -- ID of the FIRST row of the last multi-row insert (1000)
SELECT * FROM tickets;

CREATE TABLE tickets (
    ticket_id  BIGINT IDENTITY(1000, 1) PRIMARY KEY,   -- seed 1000, increment 1
    title      NVARCHAR(200) NOT NULL
);

INSERT INTO tickets (title) VALUES (N'Brake warning light');
SELECT SCOPE_IDENTITY();          -- last identity in the current scope (preferred over @@IDENTITY)

-- Insert an explicit value (e.g., data migration)
SET IDENTITY_INSERT tickets ON;
INSERT INTO tickets (ticket_id, title) VALUES (5000, N'Migrated ticket');
SET IDENTITY_INSERT tickets OFF;

-- Reseed
DBCC CHECKIDENT ('tickets', RESEED, 6000);

-- Legacy
CREATE TABLE tickets_legacy (ticket_id BIGSERIAL PRIMARY KEY, title TEXT NOT NULL);
-- Creates sequence tickets_legacy_ticket_id_seq and DEFAULT nextval('tickets_legacy_ticket_id_seq')

-- Modern (recommended)
CREATE TABLE tickets (
    ticket_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title      TEXT NOT NULL
);

INSERT INTO tickets (title) VALUES ('Brake warning light') RETURNING ticket_id;

-- After bulk-loading explicit IDs, resynchronize the sequence
SELECT setval(pg_get_serial_sequence('tickets', 'ticket_id'), (SELECT MAX(ticket_id) FROM tickets));

-- PostgreSQL
CREATE SEQUENCE invoice_no_seq START WITH 100000 INCREMENT BY 1 CACHE 50;
SELECT nextval('invoice_no_seq');        -- 100000
SELECT currval('invoice_no_seq');        -- 100000 (last value in THIS session)
INSERT INTO invoices (invoice_no, amount) VALUES (nextval('invoice_no_seq'), 2500);

-- Oracle
CREATE SEQUENCE invoice_no_seq START WITH 100000 INCREMENT BY 1 CACHE 50 NOCYCLE;
INSERT INTO invoices (invoice_no, amount) VALUES (invoice_no_seq.NEXTVAL, 2500);
SELECT invoice_no_seq.CURRVAL FROM dual;

-- SQL Server 2012+
CREATE SEQUENCE dbo.invoice_no_seq AS BIGINT START WITH 100000 INCREMENT BY 1 CACHE 50;
INSERT INTO invoices (invoice_no, amount) VALUES (NEXT VALUE FOR dbo.invoice_no_seq, 2500);

-- Sequence as a column default
CREATE TABLE invoices (
    invoice_no  BIGINT DEFAULT nextval('invoice_no_seq') PRIMARY KEY,   -- PostgreSQL
    amount      NUMERIC(12,2)
);

-- PostgreSQL 13+ (gen_random_uuid built in; older versions via pgcrypto extension)
CREATE TABLE devices (
    device_id  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name       TEXT NOT NULL
);
-- PostgreSQL 18+: uuidv7() for time-ordered UUIDs

-- SQL Server
CREATE TABLE devices (
    device_id  UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),   -- or NEWID() (random)
    name       NVARCHAR(100) NOT NULL
);

-- MySQL 8
CREATE TABLE devices (
    device_id  BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),  -- swap flag 1 improves index locality for UUIDv1
    name       VARCHAR(100) NOT NULL
);
SELECT BIN_TO_UUID(device_id, 1) AS device_id, name FROM devices;

-- Oracle
CREATE TABLE devices (device_id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY, name VARCHAR2(100));

Expected Result

ID 3 was consumed by the rolled-back insert — a normal gap.

-- PostgreSQL
CREATE TABLE demo_ids (id INT GENERATED ALWAYS AS IDENTITY, label TEXT);
INSERT INTO demo_ids (label) VALUES ('a'), ('b');
BEGIN; INSERT INTO demo_ids (label) VALUES ('c'); ROLLBACK;
INSERT INTO demo_ids (label) VALUES ('d');
SELECT * FROM demo_ids ORDER BY id;

Common Mistakes

Expecting gapless IDs from sequences/identity.

Using MAX(id) + 1 to generate IDs (race condition under concurrency).

Using @@IDENTITY in SQL Server when triggers exist.

Forgetting to reset sequences after importing data with explicit IDs (next insert fails with duplicate key).

Using random UUIDs as clustered primary keys on write-heavy tables without considering fragmentation.

Exposing sequential IDs in public URLs where enumeration is a security risk.

Choosing INT for a table that may exceed 2.1 billion rows.

Best Practices

Use BIGINT identity for large/high-growth tables.

Use GENERATED ALWAYS AS IDENTITY in PostgreSQL/Oracle for new designs.

Use UUID v7 or sequential GUIDs when global uniqueness is needed.

Retrieve generated keys with RETURNING/OUTPUT, not a follow-up MAX query.

Keep natural keys unique with UNIQUE constraints alongside surrogate IDs.

Interview Relevance

Medium: "IDENTITY vs SEQUENCE?", "Why are there gaps in my IDs?", "UUID vs integer keys?", "How do you get the last inserted ID?", "Why not use MAX(id)+1?".

Practice Questions

Create a table with an identity column starting at 500 incrementing by 5 in your DBMS.

Create a sequence shared by purchase_orders and sales_orders for a common document number.

Explain why a rolled-back insert creates a gap.

List three trade-offs of UUID primary keys.

How do you fix a PostgreSQL sequence that is behind the maximum ID after a data import?

Quiz Checkpoint 5 (Modules 23 – 29)

Q1. (Multiple choice) A table has PK (student_id, course_id) and column student_name. Which normal form is violated? A) 1NF B) 2NF C) 3NF D) None

Answer: B. student_name depends on only part of the composite key (partial dependency).

Q2. (True/False) Every table in BCNF is also in 3NF.

Answer: True. BCNF is stricter than 3NF.

Q3. (Multiple choice) How is a many-to-many relationship implemented? A) FK in both tables B) Comma-separated IDs C) Junction table D) One-to-one table Answer: C.

Q4. (Find the error) SQL Server trigger:

Answer: It assumes one row per statement. A multi-row UPDATE logs only one arbitrary order. Use INSERT INTO order_log (order_id) SELECT order_id FROM inserted;.

Q5. (True/False) A rolled-back transaction returns its consumed sequence values so they are reused.

Answer: False. Sequence values are not transactional; gaps are expected.

Q6. (Multiple choice) Which can be used directly in a SELECT list? A) Stored procedure B) Scalar function C) Trigger D) Savepoint Answer: B.

Q7. (Predict the output) A PostgreSQL temp table tmp_x is created in session 1. Session 2 runs SELECT * FROM tmp_x;. What happens?

Answer: Error: relation "tmp_x" does not exist. Temporary tables are private to the session.

Q8. (Write the query) Create a MySQL function full_name(first VARCHAR(50), last VARCHAR(50)) returning 'Last, First'.

Q9. (Multiple choice) Which statement about denormalization is correct? A) It always improves performance B) It removes redundancy C) It intentionally adds redundancy for read performance D) It is required by 3NF

Q10. (True/False) In MySQL, a trigger can update the same table that fired it.

Answer: False. MySQL raises error 1442; use a BEFORE trigger and modify NEW instead.

CREATE TRIGGER trg ON orders AFTER UPDATE AS
BEGIN
    DECLARE @id INT;
    SELECT @id = order_id FROM inserted;
    INSERT INTO order_log (order_id) VALUES (@id);
END;

DELIMITER //
CREATE FUNCTION full_name(p_first VARCHAR(50), p_last VARCHAR(50))
RETURNS VARCHAR(102)
DETERMINISTIC
BEGIN
    RETURN CONCAT(p_last, ', ', p_first);
END //
DELIMITER ;