Ограничение UNIQUE в SQLite: синтаксис и примеры

Ограничение UNIQUE обеспечивает уникальность значений в столбце или наборе столбцов, что важно для поддержания целостности данных в SQLite

Синтаксис ограничения UNIQUE в SQLite

Чтобы задать ограничение UNIQUE, используется одноимённое ключевое слово, за которым следует один или несколько столбцов

Ограничение можно определить на уровне столбца (column-level) или на уровне таблицы (table-level). Ограничение, охватывающее несколько столбцов, можно задать только на уровне таблицы

Определение на уровне столбца выглядит так:

CREATE TABLE table_name( ..., column_name type UNIQUE, ...
);

На уровне таблицы:

CREATE TABLE table_name( ..., UNIQUE (column_name)
);

Для нескольких столбцов сразу:

CREATE TABLE table_name( ..., UNIQUE (column_name1, column_name2, ...)
);

Если вы попытаетесь вставить или обновить значение, уже существующее в столбце с установленным ограничением UNIQUE, SQLite выдаст ошибку и прервёт эту операцию

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

Ограничение UNIQUE для одного столбца

Следующий оператор создаёт таблицу contacts с ограничением UNIQUE на столбце email:

CREATE TABLE contacts( contact_id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT, email TEXT NOT NULL UNIQUE
);

Вставим новую строку:

INSERT INTO contacts(first_name, last_name, email)
VALUES ('John', 'Doe', '[email protected]');

Если попытаться вставить ещё одного контакта с тем же адресом электронной почты, SQLite вернёт ошибку:

INSERT INTO contacts(first_name, last_name, email)
VALUES ('Johnny', 'Doe', '[email protected]');

SQLite прервёт операцию и сообщит о нарушении ограничения уникальности — именно так и должно работать это ограничение

Ограничение UNIQUE для нескольких столбцов

Следующий оператор создаёт таблицу shapes с составным ограничением UNIQUE по столбцам background_color и foreground_color:

CREATE TABLE shapes( shape_id INTEGER PRIMARY KEY, background_color TEXT, foreground_color TEXT, UNIQUE (background_color, foreground_color)
);

Вставим первую строку:

INSERT INTO shapes(background_color, foreground_color)
VALUES ('red', 'green');

Следующий оператор выполнится успешно, поскольку комбинация значений в столбцах background_color и foreground_color уникальна:

INSERT INTO shapes(background_color, foreground_color)
VALUES ('red', 'blue');

Однако попытка вставить строку с уже существующей парой значений приведёт к ошибке

Составное ограничение UNIQUE проверяет уникальность комбинации значений, а не отдельных столбцов. Например, значение ('red', 'blue') считается уникальным, в то время как повторное значение ('red', 'green') — нет

Ограничение UNIQUE и значения NULL

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

Создадим таблицу lists со столбцом email, на котором задано ограничение UNIQUE:

CREATE TABLE lists( list_id INTEGER PRIMARY KEY, email TEXT UNIQUE
);

Вставим несколько значений NULL в столбец email:

INSERT INTO lists(email)
VALUES (NULL), (NULL);

Запросим данные из таблицы:

SELECT * FROM lists;

При этом, несмотря на ограничение UNIQUE на столбце email, возможны несколько записей со значением NULL, что отличает SQLite от других СУБД, где разрешён только один NULL

Ошибка UNIQUE constraint failed в SQLite: причины и решения

На практике я встречаю несколько распространённых заблуждений, которые стоит разобрать отдельно

Различие между PRIMARY KEY и UNIQUE. Первичный ключ (PRIMARY KEY) автоматически создает ограничение UNIQUE, при этом не допускает значений NULL. В отличие от этого, ограничение UNIQUE может включать несколько NULL, если не указано NOT NULL

Ожидание ошибки при вставке NULL в уникальный столбец. Как уже было сказано, SQLite трактует каждый NULL как уникальное значение. Мой совет — если нужно запретить NULL, добавьте NOT NULL явно: email TEXT NOT NULL UNIQUE

Неверное понимание составного ограничения. Составное UNIQUE (col1, col2) не означает, что каждый из столбцов уникален по отдельности. Уникальной должна быть именно пара значений. Если нужна уникальность каждого столбца независимо, задайте два отдельных ограничения

Попытка добавить ограничение UNIQUE через ALTER TABLE. SQLite не поддерживает добавление ограничений через ALTER TABLE напрямую. Единственный способ — пересоздать таблицу с нужным ограничением

Как проверить наличие ограничения UNIQUE в существующей таблице

На нашем опыте удобнее всего пользоваться командой .schema в интерфейсе командной строки SQLite (SQLite CLI):

.schema contacts

Вывод покажет оператор CREATE TABLE с полным определением столбцов и ограничений. Альтернативно можно запросить системную таблицу sqlite_master:

SELECT sql FROM sqlite_master WHERE type='table' AND name='contacts';

Оба способа позволяют убедиться, что ограничение UNIQUE действительно присутствует в схеме таблицы

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

Можно ли добавить ограничение UNIQUE к уже существующей таблице в SQLite? Напрямую через ALTER TABLE — нет. Нужно создать новую таблицу с нужным ограничением, скопировать данные, удалить старую таблицу и переименовать новую

В чём разница между UNIQUE и PRIMARY KEY? PRIMARY KEY автоматически подразумевает уникальность и не допускает NULL, тогда как UNIQUE может включать несколько NULL, если не указано NOT NULL

Можно ли хранить несколько значений NULL в столбце с ограничением UNIQUE? Да, SQLite считает каждый NULL уникальным, и поэтому допускает несколько NULL в уникальном столбце

Как работает составное ограничение UNIQUE? Оно проверяет уникальность комбинации значений указанных столбцов, а не каждого столбца по отдельности. Два одинаковых значения в одном столбце допустимы, если значения во втором столбце различаются

Что произойдёт при нарушении ограничения UNIQUE? SQLite вернёт ошибку UNIQUE constraint failed и прервёт операцию вставки или обновления. Транзакция при этом откатывается, если не используется обработка конфликтов через ON CONFLICT или INSERT OR REPLACE