SQL - Using Sequences

SQL - Sequences and Auto-Incrementing Keys

A sequence is a database object that generates a series of unique integers (e.g., 1, 2, 3, …). Sequences are most commonly used to generate unique primary key values for new rows in a table, ensuring each row has a distinct identifier.

While the underlying mechanism is often a SEQUENCE object, most RDBMSs provide convenient, shorthand syntax for creating auto-incrementing columns.

Common Implementations of Auto-Incrementing Columns

The syntax for defining an auto-incrementing primary key varies by database.

Standard SQL: Identity Columns

The modern SQL standard uses GENERATED AS IDENTITY.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ProductName VARCHAR(100) NOT NULL
);

GENERATED ALWAYS means the database always generates the value. GENERATED BY DEFAULT allows you to override it by supplying your own value.

PostgreSQL: SERIAL

PostgreSQL offers a convenient shorthand SERIAL pseudo-type, which automatically creates an integer column, a sequence, and sets the column’s default value to the next value from the sequence.

CREATE TABLE Products (
    ProductID SERIAL PRIMARY KEY, -- Shorthand for an auto-incrementing integer PK
    ProductName VARCHAR(100) NOT NULL
);

MySQL: AUTO_INCREMENT

MySQL uses the AUTO_INCREMENT attribute.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    ProductName VARCHAR(100) NOT NULL
);

SQL Server: IDENTITY

SQL Server uses the IDENTITY property, where you can specify the seed (starting value) and increment.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1), -- Starts at 1, increments by 1
    ProductName VARCHAR(100) NOT NULL
);

Using Auto-Incrementing Columns

When you insert a new row, you simply omit the auto-incrementing column from your INSERT statement, and the database will automatically generate the next value for you.

-- We don't provide a value for ProductID
INSERT INTO Products (ProductName) VALUES ('Wireless Keyboard');

Retrieving the Last Generated ID

After an INSERT, it’s often necessary to get the ID that was just generated (e.g., to use as a foreign key in another table). The method for this varies.

• PostgreSQL / Standard SQL: Use the RETURNING clause. This is the most efficient method as it retrieves the ID in the same database round-trip.
• MySQL: Use the LAST_INSERT_ID() function.
• SQL Server: Use the SCOPE_IDENTITY() function.

Example with RETURNING (PostgreSQL)

INSERT INTO Products (ProductName) VALUES ('4K Monitor')
RETURNING ProductID;

Working directly with Sequences

For more advanced use cases, you can create and interact with sequence objects directly. This is useful when you need a unique number that is not tied to a table column.

-- Create a sequence (PostgreSQL/Oracle syntax)
CREATE SEQUENCE order_number_seq START WITH 1000;

-- Get the next value from the sequence
SELECT nextval('order_number_seq');