The constraints that apply when using the ON CONFLICT
clause, depends on which operation it's being used in. Currently, SQLite supports the ON CONFLICT
clause only for the following two operations:
These two are entirely separate uses of the "ON CONFLICT
" keyword, and should not be confused with each other. Different constraints apply when using either one.
UPSERT
Starting with SQLite v3.24.0, you can use the ON CONFLICT
clause in an INSERT
statement to perform an UPSERT. When performing an UPSERT, ON CONFLICT
only applies to the following two constraints:
UNIQUE
;PRIMARY KEY
.
Please note that UPSERT does not work with failed NOT NULL
, CHECK
, or FOREIGN KEY
constraints, or for constraints that are implemented using triggers.
When an INSERT
operation violates one of these constraints, the ON CONFLICT
clause can be used to specify the action to be taken. The ON CONFLICT
clause is followed by:
- A conflict target that triggers the UPSERT, and;
- A "
DO NOTHING
" or "DO UPDATE
" operation, that's to be performed when the uniqueness constraint fails on the conflict target.
The conflict target is optional for the last ON CONFLICT
clause in the INSERT
statement, in which case the ON CONFLICT
will fire if any uniqueness constraint fails.
For example, let's suppose you have the following "users
" table:
CREATE TABLE `users` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `username` TEXT UNIQUE, `date_login` DATETIME ); INSERT INTO `users` (`username`) VALUES ('john');
When you do an UPSERT operation like the following, it will perform an UPDATE
when the INSERT
violates the uniqueness constraint:
-- SQLite 3.24.0+ INSERT INTO `users` (`username`) VALUES ('john') ON CONFLICT DO UPDATE SET `date_login` = datetime('now');
This will result in "date_login
" column being updated to the current date/time as the username
"john
" already exists:
+----+----------+---------------------+ | id | username | date_login | +----+----------+---------------------+ | 1 | john | 2023-04-02 13:38:33 | +----+----------+---------------------+
Conflict Resolution
Starting with SQLite v2.3.0, you can use the ON CONFLICT
clause in CREATE TABLE
, with the following four constraints for conflict resolution:
UNIQUE
;NOT NULL
;CHECK
;PRIMARY KEY
.
Please note that the ON CONFLICT
clause does not apply to FOREIGN KEY
constraints.
When an INSERT
or UPDATE
operation violates one of these constraints, the ON CONFLICT
clause can be used to specify the action to be taken. The ON CONFLICT
clause can be followed by one of the following five conflict resolution algorithm choices:
ROLLBACK
— abort transaction with an error, and rollback the entire transaction if a unique constraint violation occurs, undoing all changes;ABORT
— abort with an error, discarding any changes made by the statement which caused the violation. The transaction remains active, and any changes made by prior statements (within the same transaction) are still committed to the database;FAIL
— abort with an error if a unique constraint violation occurs. The transaction remains active, and any changes made by prior and current statements (within the same transaction) are still committed to the database;IGNORE
— ignore the current statement if a unique constraint violation occurs, and continue executing subsequent queries;REPLACE
— replace the conflicting row with the new row being inserted or updated, effectively deleting the old row and inserting a new one.
These can be applied in the following two ways:
- Using the
ON CONFLICT
clause in theCREATE TABLE
command; - Using the
OR
keyword (instead ofON CONFLICT
) in theINSERT
andUPDATE
commands.
Using either one has the same meaning.
Please note that starting with SQLite v3.3.3, support for ON CONFLICT
clause on CREATE INDEX
was removed.
For example, if you specify ON CONFLICT ROLLBACK
in CREATE TABLE
, the entire transaction will be rolled back if a unique constraint violation occurs:
CREATE TABLE `users` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
`username` TEXT UNIQUE ON CONFLICT ROLLBACK
);
BEGIN TRANSACTION;
INSERT INTO `users` (`username`) VALUES ('john');
INSERT INTO `users` (`username`) VALUES ('john');
COMMIT;
-- Error: UNIQUE constraint failed: users.username
This means that both INSERT
statements will be rolled back, resulting in the table with no rows:
+----+----------+---------------------+ | id | username | date_login | +----+----------+---------------------+ | (empty) | +----+----------+---------------------+
This is the same as using INSERT OR ROLLBACK
command in the following way:
CREATE TABLE `users` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
`username` TEXT UNIQUE
);
BEGIN TRANSACTION;
INSERT INTO `users` (`username`) VALUES ('john');
INSERT OR ROLLBACK INTO `users` (`username`) VALUES ('john');
COMMIT;
-- Error: UNIQUE constraint failed: users.username
Please note that the algorithm specified in the "INSERT OR [algorithm]
" or "UPDATE OR [algorithm]
" command overrides any algorithm specified in a CREATE TABLE
. If no algorithm is specified anywhere, the ABORT
algorithm is used as a default.
This post was published by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.