AUTO_INCREMENT, Surrogate vs Natural Keys, and Identifier Design

Learning outcomes

Identifiers are not row counts. They are durable labels used by foreign keys, URLs, APIs, logs, caches, and indexes. MySQL’s AUTO_INCREMENT is a convenient allocator, but it does not promise a gapless sequence and should not be used as an accounting counter or proof of table cardinality.

InnoDB adds another dimension: the primary key is the clustered index, and every secondary index record carries the primary-key value needed to locate the row. Key width and insertion locality therefore affect more than aesthetics.

AUTO_INCREMENT allocates identity values

An integer column with AUTO_INCREMENT can receive a generated value when an insert omits the column or supplies the documented generating form. The generated value is unique under the table’s rules, but the sequence may contain gaps due to failed statements, rollbacks, deletes, explicit values, concurrency, or allocation behavior.

DROP TABLE IF EXISTS servicehub_lab.id_probe;CREATE TABLE servicehub_lab.id_probe (  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  label VARCHAR(80) NOT NULL,  PRIMARY KEY (id)) ENGINE=InnoDB;INSERT INTO servicehub_lab.id_probe (label) VALUES ('first');SELECT LAST_INSERT_ID() AS generated_id;INSERT INTO servicehub_lab.id_probe (label)VALUES ('second'), ('third'), ('fourth');SELECT LAST_INSERT_ID() AS first_id_from_multirow_insert;SELECT * FROM servicehub_lab.id_probe ORDER BY id;

For a multi-row insert, LAST_INSERT_ID() reports the first generated value for that statement. Its value is connection-specific, so another session inserting rows does not overwrite the current session’s LAST_INSERT_ID(). Applications should still use their driver’s documented generated-key API rather than guessing with MAX(id).

Failure drill: treating IDs as a gapless business sequence

Suppose invoices are legally required to have a controlled business document number. Reusing the table’s auto-increment primary key as that legal sequence couples persistence identity to an allocator that is explicitly not a gapless business counter.

START TRANSACTION;INSERT INTO servicehub_lab.id_probe (label) VALUES ('rolled-back');SELECT LAST_INSERT_ID() AS allocated_inside_tx;ROLLBACK;INSERT INTO servicehub_lab.id_probe (label) VALUES ('after-rollback');SELECT LAST_INSERT_ID() AS next_allocated_id;SELECT * FROM servicehub_lab.id_probe ORDER BY id;

The exact next value can depend on server/table state, but the safe invariant is that you must not expect rollback to make an allocated identifier available as a gapless accounting number. Likewise, deleting row 3 does not turn “3” into a safe reusable identity.

Surrogate key versus natural key is a design decision, not a religion

A natural key comes from the domain, such as a stable externally assigned code. A surrogate key is introduced specifically as row identity, often a numeric auto-increment value. A common MySQL design uses a surrogate primary key for InnoDB organization plus a UNIQUE constraint on the natural business identifier.

SHOW CREATE TABLE servicehub_lab.customers;SELECT INDEX_NAME, NON_UNIQUE, SEQ_IN_INDEX,       COLUMN_NAME, CARDINALITYFROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_SCHEMA='servicehub_lab'  AND TABLE_NAME='customers'ORDER BY INDEX_NAME, SEQ_IN_INDEX;

This pattern is useful when the business key is long, externally mutable, composite, or inconvenient for referencing tables. But do not automatically add meaningless surrogates everywhere. Small stable composite keys can be excellent primary keys when they match the access pattern and lifecycle.

Why InnoDB cares about primary-key shape

InnoDB stores row data in the clustered index, normally the primary key. Secondary indexes contain their own key plus the primary-key value for the row. That means a wide primary key is repeated across every secondary index entry. A 32-byte or multi-column primary key can therefore amplify storage and cache cost in a table with many secondary indexes.

Avoid unsupported claims such as “random UUIDs are always slow.” Workload, table size, page fill, caching, storage, insert concurrency, and secondary-index count all matter. The correct lesson is to understand the mechanism and measure on representative data.

Composite keys and AUTO_INCREMENT restrictions

MySQL’s rules for AUTO_INCREMENT interact with indexes and storage engines. For InnoDB, treat an auto-increment column as a simple integer identity that is indexed appropriately, normally the leading/sole primary-key column. Avoid exotic composite auto-increment patterns unless current storage-engine documentation explicitly supports the design and you have a strong reason.

SELECT TABLE_SCHEMA, TABLE_NAME, AUTO_INCREMENTFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA='servicehub_lab'  AND TABLE_NAME IN ('customers','work_orders','id_probe');SHOW CREATE TABLE servicehub_lab.id_probe;SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

AUTO_INCREMENT metadata is useful operationally but is not a trustworthy row-count substitute. Estimates and allocator state answer different questions from COUNT(*).

Connection semantics: never guess another session’s generated key

Open two clients if possible. Insert from Session A, read LAST_INSERT_ID() there, then insert from Session B. Return to Session A and observe that its connection-specific value is not replaced by Session B’s insert. This is why connection pooling requires discipline: read the generated key from the same logical database operation/connection context that performed the insert.

Session A                         Session B---------                         ---------INSERT row A;SELECT LAST_INSERT_ID(); -> A_id                                  INSERT row B;                                  SELECT LAST_INSERT_ID(); -> B_idSELECT LAST_INSERT_ID(); -> A_idDo not replace this with SELECT MAX(id).

Hands-on lab: compare key designs with evidence

1. Create id_probe and record its SHOW CREATE TABLE.
2. Insert one row and a multi-row batch; record LAST_INSERT_ID() behavior.
3. Run the rollback experiment and record observed gaps without trying to “repair” them.
4. Create a second table with a natural VARCHAR primary key and one secondary index; inspect INFORMATION_SCHEMA.STATISTICS.
5. Create a third table with a BIGINT primary key plus a unique business key; compare the DDL/index metadata.
6. If you want to benchmark UUID-like keys, generate a representative local dataset and measure insert/index size under identical conditions; do not copy benchmark numbers from the lesson.

Production judgment and references

Choose identifier strategy alongside replication, sharding, offline creation, API exposure, privacy, and data-retention requirements. A local auto-increment integer is excellent for many single-primary MySQL applications. Distributed ID generation may be justified when independent writers must create globally unique identifiers without a central round trip. Keep that architectural requirement separate from fashion.

Monitor key-range exhaustion for bounded integer types, allocator surprises after bulk imports/restores, and index growth caused by wide keys. Never renumber primary keys merely to make them visually contiguous.

The final lesson in this chapter makes another hidden dependency explicit: sql_mode. The same insert text can fail, warn, coerce, or group differently if sessions run under different mode contracts.

Authoritative references

• MySQL 8.4 Reference Manual — Using AUTO_INCREMENT
• MySQL 8.4 Reference Manual — AUTO_INCREMENT Handling in InnoDB
• MySQL 8.4 Reference Manual — LAST_INSERT_ID()
• MySQL 8.4 Reference Manual — Clustered and Secondary Indexes
• MySQL 8.4 Reference Manual — Optimizing Storage Layout for InnoDB