AUTO_INCREMENT, Sequences, Natural vs Surrogate Keys, and Identifier Strategy

Learning outcomes

ServiceHub needs IDs for customers, work orders, invoice numbers and distributed event messages. One developer insists every identifier must be AUTO_INCREMENT. Another wants a UUID for everything. Finance asks for “no gaps ever” because humans dislike skipped invoice numbers. These requirements are not interchangeable: row identity, distributed uniqueness, human document numbering and audit sequencing solve different problems.

MariaDB offers table-bound AUTO_INCREMENT, standalone sequence objects, native UUIDs and ordinary application-supplied values. AUTO_INCREMENT and sequences are allocation mechanisms, not gapless accounting ledgers. Concurrency, rollback, cache, restart and multi-node operation can all produce skipped numbers. The correct design begins by separating technical row identity from business-visible numbering.

1. AUTO_INCREMENT allocates a table-local numeric key

An AUTO_INCREMENT column asks MariaDB to generate a numeric value when an INSERT supplies NULL/DEFAULT (and, depending on SQL mode, zero). A table can have only one AUTO_INCREMENT column and it must be indexed; with InnoDB composite indexes, its position has additional rules. The generated number is useful as a compact surrogate key, but it is not a promise that values will be consecutive forever.

CREATE TABLE servicehub_sandbox.auto_id_probe (  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  description VARCHAR(80) NOT NULL,  PRIMARY KEY (id)) ENGINE=InnoDB;INSERT INTO servicehub_sandbox.auto_id_probe(description)VALUES ('first allocation');SELECT LAST_INSERT_ID() AS my_inserted_id;

LAST_INSERT_ID() is connection-specific, so another client inserting concurrently does not steal your session’s last generated value. The wrong pattern is “SELECT MAX(id)+1” or asking the server to predict the next AUTO_INCREMENT value and then racing other writers. Insert first; retrieve the value the server actually assigned.

2. Gaps are normal and can be caused by correct operation

A rolled-back transaction, failed insert, server allocation strategy, manual high value, restart or concurrent workload may leave holes. MariaDB’s AUTO_INCREMENT FAQ explicitly warns that you should not ask for the next value as though it were reserved for your transaction. A technical identity only needs uniqueness/stability; contiguity is a different requirement.

START TRANSACTION;INSERT INTO servicehub_sandbox.auto_id_probe(description)VALUES ('this row will roll back');SELECT LAST_INSERT_ID() AS allocated_inside_tx;ROLLBACK;INSERT INTO servicehub_sandbox.auto_id_probe(description)VALUES ('next committed row');SELECT LAST_INSERT_ID() AS next_committed_id;SELECT * FROM servicehub_sandbox.auto_id_probe ORDER BY id;

Exact observed values can vary with engine/version/history, so the lesson is not “the gap will always be one.” The lesson is that rolled-back or otherwise unused allocations need not be recycled. Never make legal/accounting meaning depend on AUTO_INCREMENT having no gaps.

3. MariaDB sequence objects decouple allocation from one table

A MariaDB sequence is a database object that generates numeric values independently of one table. CREATE SEQUENCE defines start, increment, min/max, cycling and cache behavior; NEXT VALUE FOR obtains a new value. A sequence can feed multiple tables or defaults, which is useful when allocation belongs to a domain rather than one table.

CREATE SEQUENCE servicehub_sandbox.work_order_seq  START WITH 100000  INCREMENT BY 1  CACHE 50  NOCYCLE;SELECT NEXT VALUE FOR servicehub_sandbox.work_order_seq AS allocated_id;SHOW CREATE SEQUENCE servicehub_sandbox.work_order_seq;

Caching improves allocation efficiency but makes “no gaps” even less realistic: cached/reserved values can be lost on restart or when cache state is discarded. Current MariaDB documentation also warns against using sequences with statement-based binary logging because obtaining a next value modifies the sequence. Treat that as a replication/version design issue to be revisited in Chapter 13.

4. Natural keys and surrogate keys solve different stability problems

A natural key comes from the business domain: a country code, externally guaranteed device serial, or other meaningful identifier. A surrogate key is generated primarily for database identity. Neither category is automatically right. Natural keys can eliminate redundant identifiers when they are truly stable and compact; they become painful when the business can rename/reissue them. Surrogate keys provide stable internal references but do not remove the need to enforce business uniqueness separately.

A common ServiceHub compromise is a numeric or UUID surrogate primary key plus a UNIQUE constraint on the external business identifier. That separates “what row is this internally?” from “what business reference must not duplicate?”

5. Multi-writer systems require allocation coordination

Classic asynchronous multi-primary patterns can avoid AUTO_INCREMENT collisions by configuring different auto_increment_increment and auto_increment_offset values. Galera can automate this coordination with wsrep_auto_increment_control, adjusting increment/offset according to cluster membership. This avoids a class of collisions, but it also makes gaps and non-consecutive values more obvious.

SHOW VARIABLES LIKE 'auto_increment_increment';SHOW VARIABLES LIKE 'auto_increment_offset';SHOW VARIABLES LIKE 'wsrep_auto_increment_control';SHOW STATUS LIKE 'wsrep_cluster_size';

On a non-Galera node, wsrep variables may be absent, disabled, or report a non-clustered state depending on packaging. Do not force-enable clustering just to run this lesson. Chapter 15 builds a disposable Galera topology and observes how membership affects allocation.

6. “Gapless” document numbers are a separate ledger problem

If regulation or business policy requires a human document sequence with explicit handling of voided numbers, model that requirement directly. Often the correct system records an allocation/void event rather than pretending database-generated technical IDs never skip. A transactionally protected numbering table can serialize issuance, but that creates contention and recovery questions; design it as business workflow, not as a primary-key trick.

CREATE TABLE servicehub_sandbox.invoices (  invoice_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  invoice_number VARCHAR(40) NULL,  customer_id BIGINT UNSIGNED NOT NULL,  PRIMARY KEY (invoice_id),  CONSTRAINT uq_invoice_number UNIQUE (invoice_number),  CONSTRAINT fk_invoice_customer    FOREIGN KEY (customer_id)    REFERENCES servicehub_sandbox.customers(customer_id)) ENGINE=InnoDB;-- invoice_id is technical identity.-- invoice_number is assigned by a separately specified business workflow.

This design makes a critical distinction visible. A failed transaction may consume an invoice_id without creating a legal invoice number. If the domain requires traceability for every issued invoice number, the issuance workflow can record cancellations/voids explicitly instead of depending on storage-engine allocation internals.

7. Application-generated identifiers: useful when ownership is outside the database

Sometimes the application must create an identifier before it can reach MariaDB—for example, an offline field device creates a work item, a message is published before the database transaction, or several regional services generate records independently. In those cases an application-generated UUID can move identity ownership outside one server allocator. MariaDB’s native UUID column still gives the database a compact, validated UUID-aware representation even when the value originates in application code.

That flexibility has costs. Random identifiers are wider than BIGINT keys and can change index locality; time-ordered UUID variants such as UUIDv7 improve ordering characteristics but still do not make an identifier a timestamp, authorization token, or business sequence. A UUID that is globally unique is not automatically secret. If an identifier appears in a public URL, access control must still verify the authenticated principal and object relationship.

CREATE TABLE servicehub_sandbox.offline_work_items (  work_item_id UUID NOT NULL,  source_device VARCHAR(80) NOT NULL,  received_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  PRIMARY KEY (work_item_id)) ENGINE=InnoDB;-- The application could bind a UUIDv7 string generated before connection.INSERT INTO servicehub_sandbox.offline_work_items(work_item_id, source_device)VALUES ('01921e85-f198-7490-9b89-7dd0d468543b','field-unit-17');

The decision therefore depends on ownership and topology. If MariaDB is the single authoritative writer and a compact internal key is sufficient, AUTO_INCREMENT is hard to beat for simplicity. If multiple disconnected components need to create stable IDs before database contact, UUID-style generation can reduce coordination. If humans need sequential document numbers, use a separate business workflow. One column should not be forced to satisfy all three requirements.

7. Hands-on lab and verification checklist

1. Create auto_id_probe, insert a row and retrieve LAST_INSERT_ID().
2. Run the rollback experiment and observe that gapless reuse is not a contract.
3. Create work_order_seq, allocate several values and inspect SHOW CREATE SEQUENCE.
4. Compare an AUTO_INCREMENT surrogate plus UNIQUE external key with a native UUID candidate.
5. Inspect auto_increment_increment/offset and, if present, wsrep allocation variables without changing cluster state.
6. Create the invoices skeleton that separates internal identity from business document number.

8. Summary and bridge

Identifier design starts by naming the requirement. AUTO_INCREMENT is a compact table-local allocator; sequences are independent numeric generators; UUIDs support distributed identity patterns; natural keys express stable business identity when it truly is stable. All allocation mechanisms can have operational tradeoffs, and numeric allocators do not promise gaplessness.

The next lesson examines another invisible contract that can change whether writes succeed or silently coerce data: SQL_MODE. You will deliberately switch strictness, conversion and compatibility modes inside isolated sessions and prove why production applications must pin and test the behavior they depend on.