AUTO_INCREMENT / IDENTITY in SQL

Introduction

AUTO_INCREMENT and IDENTITY are database features that generate numeric values for newly inserted rows. They are most often used for surrogate primary keys, allowing applications to create a row without calculating its next internal identifier.

CREATE TABLE customers (
    customer_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

INSERT INTO customers (customer_name)
VALUES ('John');

The database assigns the ID safely under concurrent writes. Terminology and syntax differ by platform, but the central idea is database-owned numeric generation.

Why Automatic IDs Are Used

Most operational entities need a stable way to identify one row. Asking each application to coordinate the next number creates races, duplicated logic, and operational risk. Database generation centralizes allocation close to the write.

The generated value is usually meaningless to the business. Its job is to remain unique and stable while names, email addresses, statuses, and other descriptive attributes change.

Terminology Differs Across Databases

MySQL uses AUTO_INCREMENT. SQL Server uses IDENTITY. PostgreSQL and modern Oracle support standards-oriented identity declarations. SQLite connects row IDs to special integer primary-key behavior.

The broad concept is similar, but override rules, reset behavior, caching, sequences, generated-value retrieval, and DDL syntax are not portable. Always verify the production engine and version.

MySQL AUTO_INCREMENT

CREATE TABLE products (
    product_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    price DECIMAL(12,2) NOT NULL
);

Normal inserts omit product_id. MySQL allocates a value and exposes driver-supported mechanisms for retrieving it. Configuration, replication mode, and explicit-value behavior should be tested for the deployed version.

SQL Server IDENTITY

CREATE TABLE customers (
    customer_id BIGINT IDENTITY(1,1) PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

IDENTITY(1,1) uses seed 1 and increment 1. Different seeds and increments are possible. Explicit insertion requires controlled special handling such as IDENTITY_INSERT, normally reserved for migration or repair.

PostgreSQL Identity Columns

CREATE TABLE customers (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

GENERATED ALWAYS strongly favors generator ownership, while GENERATED BY DEFAULT allows explicit values more readily. Identity columns supersede older serial shorthand for many modern designs, though existing schemas commonly use sequences and serial types.

Oracle Identity Columns

Modern Oracle supports identity syntax with ALWAYS, BY DEFAULT, and related options. Older systems often use a sequence plus trigger or explicit sequence reference. Migration tools must understand which mechanism owns generation and how values are returned.

Do not assume PostgreSQL and Oracle identity options have identical override or sequence semantics merely because their SQL looks similar.

SQLite Row IDs and AUTOINCREMENT

In SQLite, an INTEGER PRIMARY KEY has special rowid behavior. The optional AUTOINCREMENT keyword changes reuse guarantees and adds overhead; it is not required simply to obtain automatically assigned integers.

Understand SQLite's documented semantics before adding the keyword. Patterns learned from server databases do not map mechanically to SQLite.

Generation Is Not the Same as PRIMARY KEY

Automatic generation produces candidate values. PRIMARY KEY enforces unique, non-null row identity. A generated column is not necessarily a primary key, and a primary key does not have to be generated.

They are commonly combined because the generator supplies convenient values and the constraint protects their identity semantics. Keep both responsibilities explicit.

What Is a Surrogate Key?

A surrogate key is an artificial identifier created primarily for technical row identity rather than derived from a domain fact. An automatically generated BIGINT is a common example.

Surrogates are useful when natural identifiers are wide, composite, mutable, private, or unavailable at row creation. They simplify foreign keys, joins, ORM identity maps, and references across internal tables.

Surrogate Keys Do Not Replace Business Uniqueness

Adding customer_id prevents duplicate technical IDs but does not prevent two rows with the same governed email, employee number, tax identifier, or order number.

CREATE TABLE customers (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(254) NOT NULL,
    CONSTRAINT uq_customers_email UNIQUE (email)
);

Primary identity and business uniqueness answer different questions and often require separate constraints.

Never Calculate MAX(id) + 1

Two concurrent transactions can read the same maximum ID and attempt to insert the same next value. Locking the entire allocation path is complex and limits throughput.

Likewise, row count is not the next ID because rows can be deleted, transactions can fail, and concurrent inserts can occur. Use the database generator or a deliberately designed distributed-ID service.

Generated IDs Are Concurrency-Safe

Identity and sequence mechanisms are designed to allocate distinct values under concurrent inserts without requiring application-level next-number coordination. Their implementation prioritizes uniqueness and throughput rather than visual continuity.

This makes database-generated IDs reliable for row identity, but it does not make them a transaction sequence, event order, or business counter.

Gaps Are Normal

Failed inserts, rollbacks, sequence caching, replication, retries, and server restarts can consume values that never appear in committed rows. Deleting a row creates another visible gap.

Gaps are not corruption. Attempting to fill them or renumber rows damages stable references and can create collisions. Treat internal IDs as opaque values.

Rollback Does Not Promise ID Reuse

Most generators allocate outside ordinary transactional rollback semantics. If a transaction receives ID 105 and rolls back, a later transaction may receive 106 rather than reusing 105.

This design reduces contention and supports concurrency. A gapless sequence is a separate, usually more expensive business requirement.

Legal Document Numbers Need Separate Design

Invoices, receipts, tickets, and regulated documents may require audited numbering, jurisdictional prefixes, cancellation records, or gap explanations. An internal identity does not automatically satisfy those rules.

Use a dedicated business-numbering process with the required transaction, audit, and issuance semantics while retaining a stable internal primary key.

Internal ID Versus Business Number

An internal ID supports joins and persistence. A business number supports users, external communication, legal rules, or search. They may have different creation times and lifecycles.

Do not encode year, region, status, or customer meaning into an identity merely for display. Store those facts separately and format a business identifier through an explicit rule.

Choose INT or BIGINT Deliberately

Estimate lifetime row count, peak write rate, retention, replication, future consolidation, and test or staging consumption. INT may fit bounded reference tables, while orders, events, logs, and telemetry often justify BIGINT.

BIGINT consumes more storage in every primary, foreign, and secondary index, but changing a heavily referenced ID type after exhaustion is difficult. Add a realistic safety margin.

Monitor Identity Capacity

Track the highest allocated value and growth rate against the type's usable range. Allocation can advance faster than committed row count because of gaps, caching, or retries.

Alert early enough to widen types and dependent foreign keys through a planned migration. Capacity exhaustion is an avoidable operational incident.

Do Not Assume Start or Increment

Generators need not begin at one or increment by one. Replicated systems may allocate odd and even values, reserve blocks, use negative ranges, or begin above imported history.

Applications should treat IDs as opaque. Code that predicts the previous or next ID is fragile and usually incorrect.

ID Is Not Row Position

Relational tables have no inherent row position. A higher identity value does not mean the row is physically later, adjacent, or currently present. Deletes and concurrent transactions break such assumptions.

Use explicit ordering columns and ORDER BY whenever result sequence matters.

ID Is Not Creation or Commit Time

One transaction can allocate a lower ID, pause, and commit after another transaction with a higher ID. Bulk operations and cached ranges further weaken ordering assumptions.

Store created_at for creation time and use a purpose-built event sequence or log position when total ordering is required. Identity is not a timestamp.

DELETE, TRUNCATE, and Reset Behavior

Deleting rows normally does not reset a generator. TRUNCATE may reset or preserve it depending on DBMS and options. Rollback, replication, and dependencies can alter what operations are permitted.

Test reset behavior before using it in deployment or test automation. Never assume development behavior matches production.

Reseeding Is Potentially Dangerous

Resetting a generator below existing values can produce duplicate-key failures. Reusing old IDs can make caches, logs, events, backups, or external references point to a different entity.

Reseed only for a documented migration or controlled environment after checking maximum stored values, dependent tables, replication, and generator metadata.

Retrieving the Generated ID

The inserting application normally needs the assigned key to create children or return a resource. Use the DBMS or driver mechanism tied to that insert, not SELECT MAX(id).

INSERT INTO customers (customer_name)
VALUES ('John')
RETURNING customer_id;

PostgreSQL supports RETURNING, SQL Server supports OUTPUT, MySQL drivers expose generated IDs, and JDBC provides generated-key APIs. Use the mechanism appropriate to the platform and batch mode.

Parent-Child Insert Flow

Insert the parent, capture its generated ID, then use that value for child rows within the same transaction. If any required step fails, roll back the business operation.

Do not guess the parent key or query the latest table-wide value. The returned key belongs to the exact insert and remains correct under concurrency.

Bulk Inserts Need Explicit Mapping

Bulk operations may return keys in DBMS-specific forms, and returned order should not be assumed unless documented. Use source correlation values, staging keys, or supported RETURNING and OUTPUT patterns to map generated parents to children.

Test batch size, failure handling, retry idempotency, and generated-key association under the production driver.

Importing Historical IDs

Migrations sometimes preserve source IDs so existing relationships and external references remain stable. Other migrations regenerate IDs and maintain an old-to-new mapping table.

Both strategies require planning. Explicit identity insertion is a controlled operation, and every child reference must use the selected mapping consistently.

Synchronize the Generator After Import

If imported explicit values exceed the generator's current state, the next automatic insert may collide. After loading, compare stored maximums with sequence or identity metadata and advance the generator using supported DBMS commands.

Verify with a controlled generated insert before enabling normal traffic. Include this step in migration automation rather than relying on memory.

AUTO_INCREMENT Versus Sequence

A sequence is a reusable database object that can often allocate values independently of one column, support caching and ranges, and be referenced explicitly. Identity syntax ties generation more directly to a column's intent.

Choose based on platform, portability, ownership, migration tooling, and whether multiple tables or applications truly need coordinated allocation.

AUTO_INCREMENT Versus DEFAULT

A static DEFAULT cannot generate unique IDs. Some databases implement identity through an attached sequence and default-like expression, but the conceptual requirement is a stateful generator that returns a different value for each allocation.

Document the actual generator and its ownership rather than calling every omitted value a default.

AUTO_INCREMENT Versus UUID

Sequential integers are compact, efficient for joins, and simple within one database authority. UUIDs support decentralized generation and offline creation but are wider and can affect index locality and payload size.

Time-ordered distributed IDs offer another tradeoff. Choose from architecture, scale, merging, privacy, and generation ownership rather than fashion.

Predictable IDs Are Not an Authorization Defect

Sequential IDs are easy to guess, but every API must enforce authorization regardless of identifier format. Replacing them with UUIDs can reduce casual enumeration without fixing insecure direct-object access.

Verify ownership and permission for each requested resource. Use a separate public identifier when external contract or exposure concerns justify it.

Database-Generated Versus Application-Generated IDs

Database generation is simple and authoritative for local writes. Application generation can provide an ID before persistence, support offline workflows, and reduce coordination across regions.

Application-generated values need a proven collision strategy, consistent libraries, and database uniqueness enforcement. Select one clear owner for each identifier.

Replication and Distributed Databases

Independent writable databases can allocate overlapping integers unless ranges, increments, node prefixes, centralized services, or globally unique schemes coordinate them. Later merging exposes collisions that were locally valid.

Plan topology, failover, multi-primary behavior, and future consolidation before choosing generation. AUTO_INCREMENT is excellent within its intended authority but is not universally global.

Sequential Index Locality

Increasing numeric keys often insert near the end of a B-tree, providing good locality and compact indexes. Under very high concurrency, that location can also become a hot page depending on engine and workload.

Identity is not automatically the fastest design. Measure primary and secondary indexes, write contention, partitioning, and read patterns with production-like data.

Key Width Multiplies Through the Schema

A primary key appears in child foreign keys and may be included in secondary indexes. Choosing a wider type increases table, index, memory, cache, I/O, and network cost across all of those copies.

Balance width against capacity and distribution requirements. Do not choose SMALLINT only to save space or use the widest available type without analysis.

Not Every Table Needs an Identity Column

Junction tables and scoped facts may already have a stable composite key such as (student_id, course_id) or (warehouse_id, product_id). Adding a surrogate mechanically can hide missing business uniqueness.

An identity can still help when the relationship itself needs independent references or history, but enforce the natural combination separately when duplicates remain invalid.

Complete Constraint Design

CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_number VARCHAR(40) NOT NULL UNIQUE,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_order_customer
      FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    CONSTRAINT chk_order_status
      CHECK (status IN ('PENDING', 'PAID', 'CANCELLED'))
);

Identity generates internal IDs. PRIMARY KEY protects identity, UNIQUE protects the business number, FOREIGN KEY protects the relationship, NOT NULL protects presence, DEFAULT initializes state, and CHECK protects its domain.

Identity Is a Generation Mechanism

AUTO_INCREMENT or IDENTITY is not a general integrity constraint. It allocates values. PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL, and CHECK define valid stored states.

This distinction matters during migrations and reviews: generation can work while business duplicates or invalid relationships still enter unless the relevant constraints exist.

Test Data and Environment Differences

Tests should not depend on exact IDs such as expecting the first row to be one. Parallel tests, prior data, rollbacks, and environment resets can change generated values.

Capture returned IDs and use them as opaque references. Reset generators only in isolated test environments with explicit setup.

Monitor Generation in Production

Track capacity, duplicate-key errors, generator state, allocation latency, cache behavior, replication conflicts, and failed explicit imports. Sudden consumption can reveal retry storms or unintended inserts.

Document generator ownership, reset procedures, restore behavior, and emergency widening plans. Identity is foundational infrastructure once many tables depend on it.

GENERATED ALWAYS Versus BY DEFAULT

Standards-oriented identity implementations commonly distinguish strong generator ownership from a mode that accepts explicit values more easily. ALWAYS protects normal inserts from accidentally supplying IDs, while BY DEFAULT supports applications or migrations that intentionally provide them.

The exact override syntax and error behavior are DBMS-specific. Choose the stricter mode when the database owns identity, and permit overrides only through reviewed migration procedures. Convenience for occasional imports should not weaken every routine insert.

Seed and Increment Choices

Seed controls the starting allocation and increment controls the step. Values other than one can support legacy cutovers, reserved namespaces, or replication strategies, but they should not encode business categories.

Document why nonstandard settings exist. Applications must not assume a particular start, step, parity, or density. Capacity planning should account for the increment because a larger step consumes the numeric range faster.

Negative and Reserved Ranges

Some migrations reserve negative IDs for temporary records, system rows, test fixtures, or imported namespaces. This can work when the policy is explicit, enforced, and compatible with every foreign key and application type.

Do not invent reserved ranges casually. Sign assumptions leak into validation, APIs, analytics, and sorting. A separate status or source column often represents provenance more clearly than encoding it in the identifier.

Explicit Identity Inserts Need Control

Normal application inserts should omit database-generated IDs. Explicit values can collide with existing rows, move generator state out of sync, or impersonate historical entities. Restrict the required privileges and keep special identity-insert modes short-lived.

Migration scripts should validate duplicates, load parents before children, capture mappings, synchronize the generator, and restore ordinary protections even when a later step fails.

Transactions and Allocation Semantics

Identity allocation is often deliberately nontransactional. A transaction can receive a value that remains consumed after rollback. This prevents concurrent transactions from waiting for every earlier outcome before allocating another ID.

The row insert remains transactional even though the numbering sequence has gaps. Tests should assert persisted relationships and returned keys, not a perfectly continuous generator history.

Gapless Numbering Is a Different System

A truly gapless business sequence usually needs serialization, issuance records, cancellation handling, recovery rules, and legal audit semantics. It may reduce throughput and requires decisions about numbers reserved by failed or abandoned transactions.

Keep this process separate from surrogate identity. The internal ID can be assigned early and contain gaps, while the business document number is issued at the correct workflow point under its own controls.

One Owner for ID Generation

Problems arise when the database, ORM, application, import process, and external service all believe they own the same identifier. Select one normal generator and make every other path consume or deliberately override it through an explicit contract.

Document ownership in schema and service design. During migrations, temporary dual behavior needs a clear cutoff, compatibility tests, and monitoring so it does not become permanent ambiguity.

Internal and Public Identifiers

A compact BIGINT can remain the internal primary key while a separate UUID, opaque token, or formatted reference is exposed publicly. This allows external contracts to remain stable even if internal storage or merge strategies change.

Protect the public identifier with UNIQUE and NOT NULL as appropriate. Do not duplicate it merely for obscurity, and never treat unpredictability as a substitute for authorization.

Identity on Junction and Child Tables

A many-to-many junction often needs only a composite primary key of its two parents. An identity becomes useful when the relationship has its own lifecycle, is referenced independently, or allows repeated pairings distinguished by time or version.

If an identity is added, preserve the natural business rule with a UNIQUE constraint where duplicate pairs remain invalid. The surrogate simplifies references but does not redefine the relationship.

High-Concurrency Insert Hotspots

Sequential keys usually provide favorable B-tree locality, but very high write concurrency can concentrate inserts on the newest index pages. Engine latches, page splits, fill factors, partitioning, and storage architecture affect the outcome.

Measure before replacing the key strategy. Random identifiers distribute writes but can increase fragmentation, cache misses, key width, and downstream index cost. Every alternative moves tradeoffs rather than eliminating them.

Backup, Restore, and Cloning

A correct backup must preserve both table rows and generator state. After restore, failover, point-in-time recovery, or environment cloning, verify that the next generated value cannot collide with stored data.

Masked development copies may intentionally reseed into isolated ranges, but production restoration should follow documented DBMS procedures. Include an identity-health check in recovery testing.

Operational Diagnostics

Useful diagnostics include current generator value, maximum stored ID, allocation rate, remaining range, cache settings, failed inserts, and dependent foreign-key types. A mismatch between maximum data and generator state often signals explicit imports or incomplete recovery.

Automate these checks for high-growth tables. Alerting should provide enough lead time to investigate workload changes and perform a type-widening migration safely.

Never Renumber Rows for Appearance

Renumbering to remove gaps changes row identity and can break foreign keys, caches, URLs, events, audit logs, exports, backups, and external integrations. Even if every current table is updated, historical evidence may still refer to the original values.

Users who need tidy numbering should receive a separate business sequence or display rank. Internal identity should remain stable for the entity's lifetime.

Do Not Perform Business Arithmetic on IDs

Subtracting IDs does not reliably count records, estimate elapsed time, or measure sales volume. Gaps, rollbacks, imports, reserved ranges, and concurrent allocation make such calculations invalid. MAX(id) can assist diagnostics but is not a business metric.

Use COUNT for row counts, timestamps for time, explicit sequence fields for ordering, and modeled amounts for analytics. Treat IDs as equality tokens used to identify and join.

Widening an Identity Type

Moving from INT to BIGINT affects the parent key, generator, every child foreign key, related indexes, ORM mappings, APIs, caches, ETL schemas, and downstream warehouses. Large tables may require staged online migration or dual-column techniques.

Inventory dependencies, test locking and log volume, preserve value mappings, and deploy compatible application types before the database crosses the old limit. Early capacity monitoring makes this controlled work instead of an outage response.

Real-World Customer and Order Pattern

A customer can use a generated internal ID and a UNIQUE email or account number. An order can use its own generated ID, a customer foreign key, and a separately governed order number for users. Child items reference the order's internal ID.

This separation keeps joins compact, preserves business uniqueness, and prevents display-number policy from destabilizing relationships. Each identifier has one clear audience and lifecycle.

IDs in APIs, Logs, and Caches

Once an ID leaves the table, it can appear in routes, messages, cache keys, traces, audit records, and analytics. Keep its representation consistent and avoid lossy conversion, especially when BIGINT values pass through languages or JSON consumers with limited numeric precision.

Log enough context to diagnose a record without exposing sensitive data, and define cache invalidation around stable identity. If an external contract uses a separate public ID, preserve an explicit mapping instead of switching formats unpredictably across endpoints.

Safe Identity Migration

  1. Decide whether source IDs are preserved or regenerated.
  2. Inventory primary keys, foreign keys, APIs, events, caches, and exports.
  3. Create old-to-new mappings when regeneration is required.
  4. Load parents before dependent children.
  5. Use controlled explicit-ID features only during migration.
  6. Validate duplicates, missing mappings, and referential integrity.
  7. Advance or reseed the generator beyond imported values.
  8. Perform a controlled generated insert and rollback test.
  9. Switch writers and monitor collisions, lag, and capacity.

Common AUTO_INCREMENT and IDENTITY Mistakes

  • Calculating the next value with MAX(id) or row count.
  • Expecting no gaps or filling gaps after deletes.
  • Assuming ID is row position, creation time, or commit order.
  • Using internal IDs as legal business numbers.
  • Assuming surrogate identity enforces business uniqueness.
  • Using predictable IDs as authorization decisions.
  • Adding an identity column to every table mechanically.
  • Choosing a type without lifetime capacity analysis.
  • Importing explicit IDs without synchronizing the generator.
  • Assuming identity syntax and reset behavior are portable.
  • Reseeding production values merely for appearance.
  • Retrieving a new ID with a table-wide maximum query.

Production Design Checklist

  • Confirm the table needs a surrogate key.
  • Identify and enforce all business candidate keys separately.
  • Choose database or application ownership for generation.
  • Select INT or BIGINT from lifetime growth and safety margin.
  • Use compatible types in every foreign-key child.
  • Store creation time and business numbers independently.
  • Do not assume continuity, chronology, or default query order.
  • Define generated-key retrieval for single and batch inserts.
  • Plan imports, restores, replication, sharding, and merges.
  • Enforce authorization independently of identifier format.
  • Control reseeding and monitor generator capacity.

Interview Questions and Answers

What is AUTO_INCREMENT or IDENTITY? A database feature that allocates numeric values for new rows, commonly for surrogate keys.

Is it the same as PRIMARY KEY? No. Generation supplies values; PRIMARY KEY enforces identity.

Can values have gaps? Yes. Rollbacks, failed inserts, caching, deletes, and replication commonly create gaps.

Why not use MAX(id) + 1? Concurrent writers can calculate the same value.

How should an application get the new ID? Use RETURNING, OUTPUT, or the driver's generated-key API tied to that insert.

Does a higher ID mean a newer row? Not reliably; allocation and commit order can differ.

AUTO_INCREMENT versus UUID? Integers are compact and centralized; UUIDs support decentralized generation with wider keys and other tradeoffs.

Does every table need one? No. Natural or composite keys may already match the row's identity.

Key Takeaway

AUTO_INCREMENT and IDENTITY provide database-owned numeric allocation for new rows. They simplify surrogate identity and concurrent insertion, but they do not guarantee gapless values, chronology, business uniqueness, authorization, or global distributed identity.

RELIABLE GENERATED ID = CLEAR GENERATION OWNER
                      + STABLE SURROGATE IDENTITY
                      + SUFFICIENT TYPE CAPACITY
                      + SEPARATE BUSINESS UNIQUENESS
                      + DRIVER-SAFE KEY RETRIEVAL
                      + SYNCHRONIZED MIGRATIONS
                      + ARCHITECTURE-AWARE DISTRIBUTION
                      + CONTINUOUS CAPACITY MONITORING

Treat generated IDs as opaque internal references. Preserve their stability, retrieve them through supported mechanisms, and model business numbers, time, workflow, and security with their own explicit rules.