Sequence и Identity: автоинкрементные поля

Что такое автоинкрементные поля? 🔄

Когда нужно гарантировать уникальность записей в таблице, часто используют специальные числовые поля, которые автоматически увеличиваются при добавлении новых строк. В разных СУБД это реализовано по-своему:

PostgreSQL: SEQUENCE + SERIAL
SQL Server: IDENTITY
MySQL: AUTO_INCREMENT

Разберёмся, как это работает и какие подводные камни есть у каждого подхода.

SEQUENCE в PostgreSQL: гибкость и контроль 🎛️

В Postgres автоинкремент строится на объектах SEQUENCE — это отдельные сущности, которые генерируют числовую последовательность.

Создаём последовательность вручную:

CREATE SEQUENCE book_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

Используем её в таблице:

CREATE TABLE books (
    id INTEGER DEFAULT nextval('book_id_seq') PRIMARY KEY,
    title VARCHAR(100)
);

Но обычно используют сокращённую запись через SERIAL:

CREATE TABLE books (
    id SERIAL PRIMARY KEY,  -- Автоматически создаёт SEQUENCE
    title VARCHAR(100)
);

⚠️ Важно! SERIAL — это не тип данных, а синтаксический сахар. Фактически создаётся последовательность и привязывается к полю.

IDENTITY в SQL Server: стандарт SQL:2003 🏗️

Стандарт SQL предлагает универсальный способ через IDENTITY. SQL Server поддерживает его «из коробки»:

CREATE TABLE employees (
    id INT IDENTITY(1,1) PRIMARY KEY,  -- Старт с 1, шаг 1
    name NVARCHAR(100)
);

Преимущества перед старым подходом (SEQUENCE): - Прямая интеграция в DDL таблицы - Лучшая предсказуемость - Поддержка в стандарте SQL

При вставке просто пропускаем это поле:

INSERT INTO employees (name) VALUES ('Данила Бежин');  -- id сгенерится автоматически

AUTO_INCREMENT в MySQL: простота ⚡

MySQL использует упрощённый синтаксис для той же функциональности:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100)
);

Особенности: - Работает только с целочисленными типами - Можно переопределить вручную при вставке - Сбрасывается при TRUNCATE TABLE

Проблемы и решения 🛠️

1. Пропуски в значениях

Автоинкремент не гарантирует непрерывность! Пропуски возникают при:
Откатах транзакций
Удалении строк
Ручных вставках

2. Переполнение

тДля INT максимум — ~2 млрд. В Postgres можно сделать BIGSERIAL, в SQL Server — BIGINT IDENTITY.

3. Репликация

В кластерах нужно настраивать разные диапазоны для разных узлов.

Когда использовать? Практические кейсы 🎯

1. Суррогатные первичные ключи

Идеально для связей между таблицами.

2. Журналы событий

Где важна последовательность, но не непрерывность.

3. Чего НЕ делать

Не используйте для номеров заказов или документов — там нужны строгие правила генерации.

Пример для интернет-магазина:

-- Postgres
CREATE TABLE products (
    product_id BIGSERIAL PRIMARY KEY,  -- До 9 квадриллионов!
    sku VARCHAR(20) UNIQUE            -- Для людей
);

-- SQL Server
CREATE TABLE orders (
    order_id INT IDENTITY(100000,1) PRIMARY KEY,  -- Начинаем с 6-значного
    customer_id INT NOT NULL
);

Продвинутые техники 🚀

Переопределение последовательности
В Postgres можно изменить текущее значение:

ALTER SEQUENCE book_id_seq RESTART WITH 100;

Шардирование
Генерация ID с учётом номера шарда:

-- Например, 5-значный шард + 10-значный ID
CREATE SEQUENCE user_id_seq START WITH 10000000000 INCREMENT BY 1;

INSERT INTO users (id) VALUES 
   (50000 * 10000000000 + nextval('user_id_seq'));  -- Шард 5

Главное запомнить 💡

1. Автоинкремент ≠ порядковый номер — возможны пропуски

2. Выбор реализации зависит от СУБД:

Postgres: SEQUENCE/SERIAL
SQL Server: IDENTITY
MySQL: AUTO_INCREMENT

3. Для бизнес-идентификаторов (номера заказов) лучше использовать отдельные правила

Попробуйте создать таблицу с автоинкрементом в вашей СУБД и поэкспериментируйте с вставкой данных — это лучший способ разобраться!