SQL - Auto Increment

SQL Auto-Incrementing Keys

An auto-incrementing key is a column that automatically generates a unique, sequential numeric value for each new row inserted into a table. This is the most common strategy for creating primary keys, ensuring each row has a unique identifier without needing to manually generate it.

When a column is set to auto-increment, you should not include it in the INSERT statement’s column list. The database will handle its value generation automatically.

Implementation Across Different Databases

The syntax for creating an auto-incrementing key varies between database systems. Here’s a look at the most common implementations.

MySQL: AUTO_INCREMENT

MySQL uses the AUTO_INCREMENT attribute. By default, it starts at 1 and increments by 1.

CREATE TABLE PRODUCTS (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    ProductName VARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2)
);

-- Inserting a new row. Notice the ID column is omitted.
INSERT INTO PRODUCTS (ProductName, Price) VALUES ('Laptop', 1200.00);
-- The new row will automatically get ID = 1.

You can change the starting value for new inserts on an existing table:

ALTER TABLE PRODUCTS AUTO_INCREMENT = 100;

SQL Server: IDENTITY

SQL Server uses the IDENTITY property, which allows you to specify a seed (starting value) and an increment.

-- IDENTITY(seed, increment)
CREATE TABLE LOGS (
    LogID INT PRIMARY KEY IDENTITY(1,1),
    LogMessage VARCHAR(MAX) NOT NULL,
    LogTime DATETIME DEFAULT GETDATE()
);

-- Starts at 1, increments by 1
INSERT INTO LOGS (LogMessage) VALUES ('User logged in.');

-- Example starting at 1000 and incrementing by 5
CREATE TABLE SENSORS (
    SensorID INT PRIMARY KEY IDENTITY(1000,5),
    Reading FLOAT
);

PostgreSQL & SQL Standard: GENERATED AS IDENTITY

The modern, standard SQL way to create an auto-incrementing column is with GENERATED AS IDENTITY. This is supported by PostgreSQL, Oracle, and recent versions of SQL Server.

CREATE TABLE EMPLOYEES (
    ID INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    FirstName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL
);

-- `GENERATED ALWAYS` prevents users from inserting their own values.
-- Use `GENERATED BY DEFAULT` to allow overriding the system value.

PostgreSQL also supports the older, convenient SERIAL pseudo-type, which is a shortcut for creating an integer column linked to a sequence.

-- PostgreSQL legacy syntax
CREATE TABLE USERS (
    ID SERIAL PRIMARY KEY,
    Username VARCHAR(50) UNIQUE NOT NULL
);

Advanced Topic: UUIDs as Primary Keys

While auto-incrementing integers are simple and efficient, an increasingly popular alternative is the Universally Unique Identifier (UUID). A UUID is a 128-bit value that is statistically unique, allowing different systems to generate IDs independently without collision.

Example with PostgreSQL

-- First, ensure the pgcrypto extension is enabled in PostgreSQL
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE ARTICLES (
    ID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    Title VARCHAR(255) NOT NULL,
    Content TEXT
);

Choosing a Primary Key Strategy

Factor	Auto-Incrementing Integer	UUID
Simplicity	Very simple to implement and understand. Human-readable.	Less readable. Requires a function to generate.
Performance	Excellent. Small, fixed size. Great for indexing as it’s sequential.	Larger (16 bytes vs 4/8). Can cause index fragmentation, though modern UUID types mitigate this.
Distributed Systems	Poor. Requires a central database to generate keys, creating a bottleneck.	Excellent. Keys can be generated on any application server without coordination.
Security	Can expose information (e.g., .../users/123 reveals user count). Guessable.	Excellent. IDs are unguessable, preventing enumeration attacks.