What's the Difference Between SQLite ABORT and FAIL Conflict Resolution Algorithms?

The ABORT and FAIL constraint conflict resolution algorithms in SQLite are very similar, as both:

• Abort with a SQLITE_CONSTRAINT error if a unique constraint violation occurs;
• Keep the transaction active;
• Commit/persist changes made by prior statements within the same transaction.

However, they differ in terms of handling of changes made by the statement that caused the violation:

• FAIL preserves the changes made by the statement that causes the violation;
• ABORT discards changes made by the statement that causes the violation.

Please note that FAIL on a foreign key constraint violation will cause an ABORT.

To demonstrate this, let's suppose you have a table of unique numbers with ON CONFLICT FAIL:

-- FAIL
CREATE TABLE `numbers` (
    `num` UNIQUE ON CONFLICT FAIL
);

INSERT INTO `numbers` VALUES (1), (3), (4);

This will result in the following table of numbers:

+-----+
| num |
+-----+
|  1  |
|  3  |
|  4  |
+-----+

Suppose you run an UPDATE query, that causes a violation of the uniqueness constraint, for example, like the following:

UPDATE `numbers` SET `num` = `num` + 1 WHERE `num` <= 4
-- Error: UNIQUE constraint failed: a.num

This will fail with a SQLITE_CONSTRAINT error and update the first matching record, resulting in the following:

+-----+
| num |
+-----+
|  2  |
|  3  |
|  4  |
+-----+

Compare this to the same table of unique numbers, but with ON CONFLICT ABORT:

-- ABORT
CREATE TABLE `numbers` (
    `num` UNIQUE ON CONFLICT ABORT
);

INSERT INTO `numbers` VALUES (1), (3), (4);

Running the same UPDATE query will fail with a SQLITE_CONSTRAINT error as well, but unlike FAIL, it will not persist changes for the record that violated the uniqueness constraint:

UPDATE `numbers` SET `num` = `num` + 1 WHERE `num` <= 4
-- Error: UNIQUE constraint failed: a.num

As a result, this query will update nothing in the table:

+-----+
| num |
+-----+
|  1  |
|  3  |
|  4  |
+-----+