Оператор REPLACE в SQLite: как работает и когда использовать

Оператор REPLACE в SQLite обеспечивает надёжное вставление строк, позволяя избегать конфликтов уникальных ключей. Мы подробно рассмотрим, как он работает, в чём его отличие от оператора UPDATE и какие могут быть неожиданные последствия его использования

Как работает REPLACE в SQLite

Идея оператора REPLACE состоит в том, что при нарушении ограничения UNIQUE или PRIMARY KEY он выполняет следующее:

Во-первых, удаляет существующую строку, которая вызывает нарушение ограничения

Во-вторых, вставляет новую строку

Если при выполнении возникает нарушение ограничения, например, NOT NULL, оператор REPLACE прерывает выполнение и откатывает транзакцию

REPLACE — это не просто комбинация INSERT и UPDATE; это процесс, в котором происходит вставка новой строки, удаление существующей и повторная вставка. Такая схема имеет значение для понимания поведения базы данных при выполнении операций

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

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

Синтаксис оператора REPLACE

Полная форма записи:

INSERT OR REPLACE INTO table (column_list)
VALUES (value_list);

Сокращённая форма, которую я чаще всего использую в реальных проектах:

REPLACE INTO table (column_list)
VALUES (value_list);

Обе формы эквивалентны и дают одинаковый результат

Подготовка таблицы для примеров

Чтобы разобраться в поведении оператора, создадим таблицу positions (должности) и наполним её тестовыми данными

Во-первых, создайте таблицу со следующей структурой:

CREATE TABLE IF NOT EXISTS positions ( id INTEGER PRIMARY KEY, title TEXT NOT NULL, min_salary NUMERIC
);

Во-вторых, вставьте несколько строк в таблицу positions:

INSERT INTO positions (title, min_salary)
VALUES ('DBA', 120000), ('Developer', 100000), ('Architect', 150000);

В-третьих, проверьте вставку с помощью оператора SELECT (выборка):

SELECT * FROM positions;

Следующий оператор создаёт уникальный индекс (unique index) по столбцу title таблицы positions, чтобы гарантировать отсутствие дублирующихся названий должностей:

CREATE UNIQUE INDEX idx_positions_title
ON positions (title);

Теперь таблица готова к экспериментам с REPLACE

Примеры использования REPLACE в SQLite

Вставка новой строки

Предположим, вы хотите добавить должность в таблицу positions, если она не существует, а если должность существует — обновить текущую запись

Следующий оператор REPLACE вставляет новую строку, поскольку должность Full Stack Developer отсутствует в таблице positions:

REPLACE INTO positions (title, min_salary)
VALUES ('Full Stack Developer', 140000);

Проверьте результат операции:

SELECT id, title, min_salary
FROM positions;

Поскольку конфликта уникальности не возникло, SQLite просто добавил новую строку — поведение идентично обычному INSERT

Замена существующей строки

Рассмотрим следующий оператор:

REPLACE INTO positions (title, min_salary)
VALUES ('DBA', 170000);

Вот что происходит внутри:

Во-первых, SQLite проверил ограничение UNIQUE

Во-вторых, поскольку этот оператор нарушил ограничение UNIQUE, попытавшись добавить название DBA, которое уже существует, SQLite удалил существующую строку

В-третьих, SQLite вставил новую строку с данными, предоставленными оператором REPLACE

Обратите внимание: значение id у новой строки будет отличаться от исходного, потому что старая строка была удалена и создана заново. Это ключевое отличие REPLACE от UPDATE

Случай с нарушением NOT NULL

REPLACE INTO positions (id, min_salary)
VALUES (2, 110000);

Этот оператор пытался обновить значение min_salary для должности с id 2, которой является Developer (разработчик)

Во-первых, поскольку должность с id 2 уже существует, оператор REPLACE удаляет её

Затем SQLite попытался вставить новую строку с двумя столбцами: (id, min_salary). Однако это нарушает ограничение NOT NULL столбца title. Поэтому SQLite откатывает транзакцию

Если столбец title не имеет ограничения NOT NULL, оператор REPLACE вставит новую строку, в которой столбец title будет иметь значение NULL

Чем REPLACE отличается от UPDATE в SQLite

Это различие стоит зафиксировать отдельно, потому что оно часто становится источником ошибок

Оператор REPLACE выполняет удаление существующей строки и создание новой вместо обновления. В случае конфликта ключа старые данные удаляются

• Значение id (если оно генерируется автоматически) изменится
• Все столбцы, не указанные в операторе REPLACE, получат значения по умолчанию или NULL, а не сохранят прежние значения
• Внешние ключи, ссылающиеся на удалённую строку, могут нарушиться

Если нужно обновить только часть полей без утраты остальных данных, лучше использовать комбинацию INSERT OR IGNORE и UPDATE или оператор UPSERT, введённый в SQLite 3.24

Типичные ошибки при использовании REPLACE

Зная поведение оператора, можно заранее избежать нескольких распространённых проблем

Некоторые разработчики ошибочно полагают, что команда REPLACE INTO positions (id, min_salary) VALUES (2, 110000) изменит только зарплату. Однако в результате удаления строки столбец title станет NULL или операция будет откатана, если для него установлено ограничение NOT NULL

Потеря автоинкрементного id. После выполнения REPLACE строка получает новый id, что может нарушить внешние ключи и привести к потере ссылочной целостности в связанных таблицах

Отсутствие уникального индекса. Если на таблице нет ограничения UNIQUE или PRIMARY KEY, REPLACE всегда будет работать как обычный INSERT и никогда не заменит существующую строку

Нарушение NOT NULL на втором шаге. Если в операторе REPLACE не указаны все обязательные столбцы, транзакция откатится — строка будет удалена, но новая не вставится. Это приводит к потере данных

Когда использовать REPLACE, а когда — UPSERT

REPLACE удобен, когда:

• Вы передаёте полный набор значений для всех столбцов
• Потеря старого id не критична
• Таблица не имеет зависимых внешних ключей

UPSERT (INSERT ... ON CONFLICT DO UPDATE) предпочтительнее, когда:

• Нужно обновить только часть столбцов при конфликте
• Важно сохранить исходный id строки
• Таблица участвует в связях через внешние ключи

Я рекомендую использовать UPSERT в большинстве современных сценариев — он точнее выражает намерение и не приводит к неожиданной потере данных. На моей практике именно путаница между REPLACE и UPSERT чаще всего становится причиной трудноуловимых багов с нарушением ссылочной целостности

Ответы на эти вопросы могут быть для вас полезными

Чем REPLACE отличается от UPDATE в SQLite? REPLACE удаляет конфликтующую строку и вставляет новую, тогда как UPDATE изменяет существующую строку на месте. При использовании REPLACE строка получает новый rowid, а все неуказанные столбцы сбрасываются в NULL или значение по умолчанию

Что произойдёт, если при REPLACE нарушится ограничение NOT NULL? SQLite откатит транзакцию: старая строка будет удалена, но новая не вставится. Чтобы избежать этого, всегда указывайте в операторе REPLACE все столбцы с ограничением NOT NULL

Изменится ли значение id после выполнения REPLACE? Да. Поскольку REPLACE физически удаляет старую строку и создаёт новую, автоинкрементный id изменится. Если сохранение id критично, используйте UPSERT

Работает ли REPLACE без уникального индекса или PRIMARY KEY? Без ограничения уникальности REPLACE ведёт себя как обычный INSERT и никогда не заменяет существующие строки — конфликта просто не возникает

Когда лучше использовать UPSERT вместо REPLACE? Когда нужно обновить только часть столбцов при конфликте, сохранить исходный id или работать с таблицей, на которую ссылаются внешние ключи. Синтаксис: INSERT INTO ... ON CONFLICT DO UPDATE SET ...