Оператор ON CONFLICT

conflict-clause: скрыть

Оператор ON CONFLICT — это нестандартное расширение, специфичное для SQLite, которое может использоваться во многих других SQL-командах. Он выделен в отдельном разделе, так как не является частью стандартного SQL и, возможно, не знаком.

Оператор ON CONFLICT, описанный здесь, присутствует в SQLite с версии 3.0.0 (18.06.2004). Фраза "ON CONFLICT" также используется в операторе UPSERT, который является расширением оператора INSERT, добавленным в версии 3.24.0 (04.06.2018). Не путайте эти два отдельных использования фразы "ON CONFLICT".

Синтаксис оператора ON CONFLICT, как показано выше, используется для команды CREATE TABLE. Для команд INSERT и UPDATE ключевые слова "ON CONFLICT" заменяются на "OR", чтобы синтаксис был более естественным. Например, вместо "INSERT ON CONFLICT IGNORE" используется "INSERT OR IGNORE". Ключевые слова изменяются, но смысл оператора остается тем же.

Оператор ON CONFLICT применяется к ограничениям UNIQUE, NOT NULL, CHECK и PRIMARY KEY. Алгоритм ON CONFLICT не применяется к ограничениям FOREIGN KEY.

Существует пять алгоритмов разрешения конфликтов: ROLLBACK, ABORT, FAIL, IGNORE и REPLACE. По умолчанию используется алгоритм ABORT. Вот что они означают:

ROLLBACK

При возникновении нарушения соответствующего ограничения алгоритм ROLLBACK прерывает текущую SQL-команду с ошибкой SQLITE_CONSTRAINT и откатывает текущую транзакцию. Если активная транзакция отсутствует (кроме подразумеваемой транзакции, создаваемой при каждой команде), алгоритм ROLLBACK работает так же, как алгоритм ABORT.

ABORT

При возникновении нарушения соответствующего ограничения алгоритм ABORT прерывает текущую SQL-команду с ошибкой SQLITE_CONSTRAINT и отменяет любые изменения, внесенные текущей SQL-командой; но изменения, внесенные предыдущими SQL-командами в рамках той же транзакции, сохраняются, и транзакция остается активной. Это поведение по умолчанию и поведение, определенное SQL-стандартом.

FAIL

При возникновении нарушения соответствующего ограничения алгоритм FAIL прерывает текущую SQL-команду с ошибкой SQLITE_CONSTRAINT. Однако алгоритм FAIL не отменяет предыдущие изменения SQL-команды, которая завершилась неудачно, и не завершает транзакцию. Например, если команда UPDATE столкнулась с нарушением ограничения на 100-й строке, которую она пытается обновить, то изменения первых 99 строк сохраняются, но изменения строк 100 и далее никогда не происходят.

Поведение FAIL работает только для ограничений уникальности, NOT NULL и CHECK. Нарушение ограничения внешнего ключа приводит к ABORT.

IGNORE

При возникновении нарушения соответствующего ограничения алгоритм IGNORE пропускает одну строку, содержащую нарушение ограничения, и продолжает обработку последующих строк SQL-команды, как будто ничего не произошло. Другие строки до и после строки, содержащей нарушение ограничения, вставляются или обновляются нормально. Ошибка не возвращается для ошибок ограничений уникальности, NOT NULL и UNIQUE, когда используется алгоритм IGNORE. Однако алгоритм IGNORE работает как ABORT для ошибок ограничений внешнего ключа.

REPLACE

При нарушении ограничения UNIQUE или PRIMARY KEY алгоритм REPLACE удаляет существующие строки, которые вызывают нарушение ограничения, перед вставкой или обновлением текущей строки, и команда продолжает выполняться нормально. Если происходит нарушение ограничения NOT NULL, алгоритм REPLACE заменяет значение NULL на значение по умолчанию для этого столбца или, если столбец не имеет значения по умолчанию, используется алгоритм ABORT. Если происходит нарушение ограничения CHECK или ограничения внешнего ключа, алгоритм REPLACE работает как ABORT.

Когда стратегия разрешения конфликтов REPLACE удаляет строки для удовлетворения ограничения, триггеры удаления срабатывают только при включенных рекурсивных триггерах.

Обработчик обновления не вызывается для строк, которые удаляются стратегией разрешения конфликтов REPLACE. Также REPLACE не увеличивает счётчик изменений changes. Исключение, определённое в этом абзаце, может измениться в будущей версии.

В указанном алгоритме в операторе OR команды INSERT или UPDATE переопределяется любой алгоритм, указанный в CREATE TABLE. Если нигде не указан алгоритм, используется алгоритм ABORT.