Исполняемые доки SQLite: UNIQUE в SQLite

Q: Как добавить ограничение UNIQUE в SQLite?

Допишите UNIQUE к описанию столбца (email TEXT UNIQUE) или используйте конструкцию уровня таблицы — UNIQUE(col1, col2) — для уникальности по нескольким полям. Под капотом SQLite создаёт уникальный индекс и отклоняет любой INSERT или UPDATE, который привёл бы к дубликату.

Q: Чем UNIQUE отличается от PRIMARY KEY в SQLite?

PRIMARY KEY в таблице может быть только один, а ограничений UNIQUE — сколько угодно. Кроме того, первичный ключ автоматически подразумевает NOT NULL (в strict-таблицах и для INTEGER PRIMARY KEY), тогда как столбец с UNIQUE спокойно хранит несколько NULL. Иначе говоря: PRIMARY KEY — это идентификатор строки, а UNIQUE — для остальных полей, которые не должны повторяться.

Q: Почему SQLite разрешает несколько NULL в столбце с UNIQUE?

Дело в том, что в SQL NULL означает «неизвестно», а два неизвестных значения не считаются равными между собой. Поэтому в столбец с UNIQUE можно вставить сколько угодно строк с NULL — уникальными должны быть только конкретные значения. Если хочется ограничиться максимум одним NULL, добавьте NOT NULL или сделайте частичный уникальный индекс.

Q: Как исправить ошибку «UNIQUE constraint failed»?

Эта ошибка означает, что INSERT или UPDATE пытается записать дубликат в столбец с UNIQUE (или PRIMARY KEY). Варианты: поменять вставляемое значение, удалить уже существующую строку или применить INSERT ... ON CONFLICT (upsert) — так вы сами скажете SQLite, что делать при конфликте.

UNIQUE — значит «дубликаты запрещены»

Ограничение UNIQUE в SQLite говорит движку, что значения в столбце (или в группе столбцов) не должны повторяться от строки к строке. Именно так вы сообщаете базе: «два пользователя не могут иметь одинаковый email» или «код товара встречается не больше одного раза».

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    name  TEXT
);

INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada');
INSERT INTO users (email, name) VALUES ('boris@example.com', 'Boris');
INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada Two');

SELECT * FROM users;

Третий INSERT падает с ошибкой UNIQUE constraint failed: users.email. SQLite проверяет ограничение при каждой записи и отклоняет всё, что создало бы дубликат. Первые две строки сохранятся, а третья просто не доедет до таблицы.

Под капотом UNIQUE — это уникальный индекс, та же структура данных, которую SQLite использует для быстрого поиска. Поэтому проверка обходится дёшево, а столбец автоматически получает индекс.

Синтаксис: на уровне столбца или на уровне таблицы

Объявить UNIQUE можно двумя способами: прямо рядом со столбцом или отдельным блоком в конце определения таблицы:

-- Уровень столбца: просто, один столбец.
CREATE TABLE products (
    sku   TEXT UNIQUE,
    name  TEXT
);

-- Уровень таблицы: тот же эффект, другое размещение.
CREATE TABLE products2 (
    sku   TEXT,
    name  TEXT,
    UNIQUE(sku)
);

Для одного столбца оба варианта равноценны — выбирайте тот, который читается лучше. А вот табличная форма становится по-настоящему необходимой, как только уникальность нужна сразу по нескольким столбцам.

Составной уникальный ключ в SQLite: UNIQUE по нескольким столбцам

Бывает, что отдельный столбец сам по себе не уникален, но комбинация значений должна быть таковой. Один пользователь может записаться на много курсов, а на один курс — много пользователей, но одна и та же пара (user_id, course_id) не должна встречаться дважды:

CREATE TABLE enrollments (
    id        INTEGER PRIMARY KEY,
    user_id   INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    UNIQUE(user_id, course_id)
);

INSERT INTO enrollments (user_id, course_id) VALUES (1, 100);
INSERT INTO enrollments (user_id, course_id) VALUES (1, 101);  -- нормально, другой курс
INSERT INTO enrollments (user_id, course_id) VALUES (2, 100);  -- нормально, другой пользователь
INSERT INTO enrollments (user_id, course_id) VALUES (1, 100);  -- ошибка: пара уже существует

SELECT * FROM enrollments;

Ограничение действует на пару значений, а не на каждый столбец по отдельности. Пользователь 1 может записаться на сколько угодно курсов, курс 100 может собрать сколько угодно слушателей — но конкретная связка «пользователь–курс» может встретиться только один раз.

Это классический приём для таблиц-связок в отношениях «многие ко многим».

UNIQUE vs PRIMARY KEY в SQLite

Звучат похоже, и они действительно родственники, но это не одно и то же:

В таблице может быть максимум один PRIMARY KEY, а вот UNIQUE-ограничений — сколько угодно.
PRIMARY KEY — это идентичность строки: на него ссылаются внешние ключи, его дублирует rowid.
UNIQUE — это просто «такое значение (или комбинация) не повторяется».
В обычной таблице столбец с UNIQUE спокойно принимает NULL; у PRIMARY KEY так не получится (есть одно историческое исключение, его мы здесь не разбираем).

Типичный пример:

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,   -- внутренний идентификатор, никогда не меняется
    email    TEXT NOT NULL UNIQUE,  -- идентификатор для пользователя, должен быть уникальным
    username TEXT NOT NULL UNIQUE   -- ещё один уникальный логин
);

id — это то, на что ссылается вся остальная база. email и username уникальны потому, что так требует приложение, а не потому, что они идентифицируют запись. Если пользователь сменит email, id останется прежним — ради этого их и разделяют.

Нюанс с NULL

На этом спотыкаются почти все, кто сталкивается с UNIQUE впервые. Столбец с ограничением UNIQUE в SQLite спокойно принимает сколько угодно значений NULL:

CREATE TABLE invites (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);

INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES ('ada@example.com');
INSERT INTO invites (email) VALUES ('ada@example.com');  -- этот завершится ошибкой

SELECT * FROM invites;

Три NULL — без проблем. А вот два 'ada@example.com' — уже конфликт.

Дело вот в чём: SQL воспринимает NULL как «неизвестно», а два неизвестных значения не считаются равными — поэтому проверка уникальности просто не может объявить их дубликатами. Если нужен максимум один NULL, проще всего поставить NOT NULL UNIQUE. А если NULL допустимы, но только по одному на каждую комбинацию остальных столбцов — пригодится частичный индекс (про это поговорим позже, в главе про индексы).

Обработка конфликтов: ON CONFLICT

По умолчанию нарушение UNIQUE прерывает выполнение запроса. Но иногда хочется поведения поинтереснее — заменить старую строку, проигнорировать новую или обновить отдельные столбцы. В SQLite для этого есть два способа.

Первый — встроить логику прямо в ограничение через ON CONFLICT:

CREATE TABLE settings (
    key   TEXT UNIQUE ON CONFLICT REPLACE,
    value TEXT
);

INSERT INTO settings (key, value) VALUES ('theme', 'light');
INSERT INTO settings (key, value) VALUES ('theme', 'dark');   -- заменяет первую запись
INSERT INTO settings (key, value) VALUES ('lang', 'en');

SELECT * FROM settings;

При повторной вставке ключа theme старая строка удаляется, а на её место встаёт новая. Из других вариантов доступны IGNORE (молча пропустить), ABORT (поведение по умолчанию), FAIL и ROLLBACK.

Второй подход работает на уровне отдельного запроса — это синтаксис upsert. Обычно он удобнее, потому что позволяет обновлять конкретные столбцы:

CREATE TABLE counters (
    name  TEXT UNIQUE,
    count INTEGER NOT NULL DEFAULT 0
);

INSERT INTO counters (name, count) VALUES ('hits', 1);
INSERT INTO counters (name, count) VALUES ('hits', 1)
    ON CONFLICT(name) DO UPDATE SET count = count + 1;
INSERT INTO counters (name, count) VALUES ('hits', 1)
    ON CONFLICT(name) DO UPDATE SET count = count + 1;

SELECT * FROM counters;

Первый INSERT создаёт строку. Следующие два натыкаются на ограничение UNIQUE и проваливаются в ветку DO UPDATE, увеличивая count. Это и есть паттерн upsert через INSERT ... ON CONFLICT — ему посвящена отдельная страница чуть дальше.

UNIQUE-ограничение и UNIQUE-индекс: в чём разница

CREATE UNIQUE INDEX решает ту же задачу, что и ограничение UNIQUE. По сути, ограничение UNIQUE под капотом само создаёт уникальный индекс — это практически один и тот же механизм, просто в разной обёртке.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_users_email ON users(email);

INSERT INTO users (email) VALUES ('ada@example.com');
INSERT INTO users (email) VALUES ('ada@example.com');  -- ошибка

Когда что выбирать:

Ограничение (CONSTRAINT) — если уникальность является частью определения таблицы. Тогда правило задокументировано прямо рядом со столбцами.
Уникальный индекс — если нужен частичный индекс (с условием WHERE), хочется задать конкретное имя или добавить ограничение к уже существующей таблице, не переписывая её. ALTER TABLE в SQLite не умеет добавлять ограничения, но индекс можно навесить всегда.

При записи поведение абсолютно одинаковое. Разница только в том, где именно в схеме будет жить это правило.

Как добавить UNIQUE к существующей таблице

ALTER TABLE в SQLite намеренно урезан — никакого ALTER TABLE ... ADD CONSTRAINT тут нет. Остаются два рабочих варианта:

-- Вариант 1: добавить уникальный индекс. Работает с существующими таблицами, та же проверка.
CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    email TEXT
);

INSERT INTO customers (email) VALUES ('ada@example.com');
INSERT INTO customers (email) VALUES ('boris@example.com');

CREATE UNIQUE INDEX idx_customers_email ON customers(email);

INSERT INTO customers (email) VALUES ('ada@example.com');  -- теперь падает

Вариант 2 — если очень хочется, чтобы UNIQUE был зашит прямо в определении таблицы — это танец с пересозданием таблицы: создаём новую таблицу с нужным ограничением, переливаем туда данные, удаляем старую, переименовываем. Об этом — на следующей странице.

Маленькое предупреждение: если вы пытаетесь навесить уникальность на столбец, в котором уже есть дубликаты, CREATE UNIQUE INDEX упадёт с ошибкой. Сначала почистите дубли, и только потом создавайте индекс.

Когда UNIQUE срабатывает: разбираем ошибку

Сообщение об ошибке прямо говорит, какое именно ограничение нарушено:

Error: UNIQUE constraint failed: users.email
Error: UNIQUE constraint failed: enrollments.user_id, enrollments.course_id

Первая форма — это ограничение на одиночный столбец users.email. Вторая — составное: оба столбца указаны потому, что уже существует именно их комбинация. Когда видите такое:

Найдите строку, в которой лежит конфликтующее значение (SELECT ... WHERE email = '...').
Решите, что делать дальше: обновить эту строку, пропустить вставку или подставить другое значение.
Если дубликаты ожидаемы и вы хотите их объединять, используйте INSERT ... ON CONFLICT DO UPDATE.

Ошибка кричит громко, и это правильно — в большинстве случаев вы действительно хотите о ней знать. Молчаливые дубликаты обошлись бы дороже, чем упавшая вставка.

Дальше: удаление и изменение таблиц

Ограничение UNIQUE нельзя просто так навесить на существующую таблицу через ALTER TABLE. Именно из-за этого ограничения в SQLite есть особый ритуал для изменения схемы — переписывание таблицы. Об этом и поговорим на следующей странице, заодно разберём, как аккуратно удалять таблицы.

Часто задаваемые вопросы

Как добавить ограничение UNIQUE в SQLite?

Допишите UNIQUE к описанию столбца (email TEXT UNIQUE) или используйте конструкцию уровня таблицы — UNIQUE(col1, col2) — для уникальности по нескольким полям. Под капотом SQLite создаёт уникальный индекс и отклоняет любой INSERT или UPDATE, который привёл бы к дубликату.

Чем UNIQUE отличается от PRIMARY KEY в SQLite?

PRIMARY KEY в таблице может быть только один, а ограничений UNIQUE — сколько угодно. Кроме того, первичный ключ автоматически подразумевает NOT NULL (в strict-таблицах и для INTEGER PRIMARY KEY), тогда как столбец с UNIQUE спокойно хранит несколько NULL. Иначе говоря: PRIMARY KEY — это идентификатор строки, а UNIQUE — для остальных полей, которые не должны повторяться.

Почему SQLite разрешает несколько NULL в столбце с UNIQUE?

Дело в том, что в SQL NULL означает «неизвестно», а два неизвестных значения не считаются равными между собой. Поэтому в столбец с UNIQUE можно вставить сколько угодно строк с NULL — уникальными должны быть только конкретные значения. Если хочется ограничиться максимум одним NULL, добавьте NOT NULL или сделайте частичный уникальный индекс.

Как исправить ошибку «UNIQUE constraint failed»?

Эта ошибка означает, что INSERT или UPDATE пытается записать дубликат в столбец с UNIQUE (или PRIMARY KEY). Варианты: поменять вставляемое значение, удалить уже существующую строку или применить INSERT ... ON CONFLICT (upsert) — так вы сами скажете SQLite, что делать при конфликте.

Связанные концепции

UNIQUE в SQLite: уникальные поля, составные ключи и NULL