Which Constraints Does the "ON CONFLICT" Clause Apply to in SQLite?

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:

1. UPSERT;
2. Conflict Resolution.

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:

1. UNIQUE;
2. 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:

1. UNIQUE;
2. NOT NULL;
3. CHECK;
4. 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:

1. ROLLBACK — abort transaction with an error, and rollback the entire transaction if a unique constraint violation occurs, undoing all changes;
2. 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;
3. 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;
4. IGNORE — ignore the current statement if a unique constraint violation occurs, and continue executing subsequent queries;
5. 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:

1. Using the ON CONFLICT clause in the CREATE TABLE command;
2. Using the OR keyword (instead of ON CONFLICT) in the INSERT and UPDATE 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.