SQLite has the ON CONFLICT clause that allows you to specify how to handle constraint conflicts. It applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints (but not FOREIGN KEY constraints).
There are five possible options you can use with this clause:
ABORTFAILIGNOREREPLACEROLLBACK
This article provides examples and an explanation of each of these options.
The ON CONFLICT clause is used in CREATE TABLE statements, but it can also be used when inserting or updating data by replacing ON CONFLICT with OR.
When Creating the Table
As mentioned, you can use ON CONFLICT when you create the table or when you insert/update data.
Here’s an example of using ON CONFLICT at the time of creating the table.
CREATE TABLE Products(
ProductId INTEGER PRIMARY KEY,
ProductName NOT NULL ON CONFLICT IGNORE,
Price
);When you use the ON CONFLICT clause, you apply it to the specific constraint that you want to handle. In this case, I added the clause to a NOT NULL constraint.
In this case I specified IGNORE, which means that, if there’s a constraint violation SQLite will skip over that row and then continue processing.
Now if I try to insert NULL into the ProductName column that row is skipped.
INSERT INTO Products VALUES
(1, 'Hammer', 9.99),
(2, NULL, 1.49),
(3, 'Saw', 11.34),
(4, 'Wrench', 37.00),
(5, 'Chisel', 23.00),
(6, 'Bandage', 120.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 9.99 3 Saw 11.34 4 Wrench 37.0 5 Chisel 23.0 6 Bandage 120.0
When Inserting Data
You can also use this clause when inserting and updating data. The difference is that, you replace ON CONFLICT with OR.
To demonstrate, I’ll drop the previous table and create it again, but without the ON CONFLICT clause:
DROP TABLE IF EXISTS Products;
CREATE TABLE Products(
ProductId INTEGER PRIMARY KEY,
ProductName NOT NULL,
Price
);Now I’ll insert the same data and use OR IGNORE to skip over the row that violates the constraint.
INSERT OR IGNORE INTO Products VALUES
(1, 'Hammer', 9.99),
(2, NULL, 1.49),
(3, 'Saw', 11.34),
(4, 'Wrench', 37.00),
(5, 'Chisel', 23.00),
(6, 'Bandage', 120.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 9.99 3 Saw 11.34 4 Wrench 37.0 5 Chisel 23.0 6 Bandage 120.0
So we get the same result as in the previous example.
In these examples I used the IGNORE option. This is just one of five possible options for this clause.
Below are examples using each of the five options.
Abort
This option aborts the current SQL statement with an SQLITE_CONSTRAINT error and backs out any changes made by the current SQL statement; but changes caused by prior SQL statements within the same transaction are preserved and the transaction remains active.
This is the default behaviour. In other words, this is what happens during constraint violations when you don’t use the ON CONFLICT clause.
Here’s an example of what happens when you specify ABORT.
DELETE FROM Products;
INSERT OR ABORT INTO Products VALUES
(1, 'Hammer', 9.99),
(2, NULL, 1.49),
(3, 'Saw', 11.34),
(4, 'Wrench', 37.00),
(5, 'Chisel', 23.00),
(6, 'Bandage', 120.00);
SELECT * FROM Products;Result:
No results were returned because the INSERT operation was aborted, and the table is therefore empty.
Here’s what happens if I put each row in its own INSERT statement within a transaction.
BEGIN TRANSACTION;
INSERT OR ABORT INTO Products VALUES (1, 'Hammer', 9.99);
INSERT OR ABORT INTO Products VALUES (2, NULL, 1.49);
INSERT OR ABORT INTO Products VALUES (3, 'Saw', 11.34);
INSERT OR ABORT INTO Products VALUES (4, 'Wrench', 37.00);
INSERT OR ABORT INTO Products VALUES (5, 'Chisel', 23.00);
INSERT OR ABORT INTO Products VALUES (6, 'Bandage', 120.00);
COMMIT;
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 9.99 3 Saw 11.34 4 Wrench 37.0 5 Chisel 23.0 6 Bandage 120.0
Fail
The FAIL option aborts the current SQL statement with an SQLITE_CONSTRAINT error. But it does not back out prior changes of the SQL statement that failed nor does it end the transaction.
Here’s an example.
DELETE FROM Products;
INSERT OR FAIL INTO Products VALUES
(1, 'Hammer', 9.99),
(2, NULL, 1.49),
(3, 'Saw', 11.34),
(4, 'Wrench', 37.00),
(5, 'Chisel', 23.00),
(6, 'Bandage', 120.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 9.99
Here it is in with separate INSERT statements within a transaction.
DELETE FROM Products;
BEGIN TRANSACTION;
INSERT OR FAIL INTO Products VALUES (1, 'Hammer', 9.99);
INSERT OR FAIL INTO Products VALUES (2, NULL, 1.49);
INSERT OR FAIL INTO Products VALUES (3, 'Saw', 11.34);
INSERT OR FAIL INTO Products VALUES (4, 'Wrench', 37.00);
INSERT OR FAIL INTO Products VALUES (5, 'Chisel', 23.00);
INSERT OR FAIL INTO Products VALUES (6, 'Bandage', 120.00);
COMMIT;
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 9.99 3 Saw 11.34 4 Wrench 37.0 5 Chisel 23.0 6 Bandage 120.0
Ignore
The IGNORE option skips the one row that contains the constraint violation and continues processing subsequent rows of the SQL statement as if nothing went wrong. Other rows before and after the row that contained the constraint violation are inserted or updated normally. No error is returned for uniqueness, NOT NULL, and UNIQUE constraint errors when this option is used. However, this option works like ABORT for foreign key constraint errors.
The first examples on this page use IGNORE, but here it is again.
DELETE FROM Products;
INSERT OR IGNORE INTO Products VALUES
(1, 'Hammer', 9.99),
(2, NULL, 1.49),
(3, 'Saw', 11.34),
(4, 'Wrench', 37.00),
(5, 'Chisel', 23.00),
(6, 'Bandage', 120.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 9.99 3 Saw 11.34 4 Wrench 37.0 5 Chisel 23.0 6 Bandage 120.0
Replace
The REPLACE option works differently depending on the violation:
- When a
UNIQUEorPRIMARY KEYconstraint violation occurs,, theREPLACEoption deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally. - If a
NOT NULLconstraint violation occurs, it replaces theNULLvalue with the default value for that column, or if the column has no default value, then theABORTalgorithm is used. - If a
CHECKconstraint or foreign key constraint violation occurs, thenREPLACEworks likeABORT.
Also, if it deletes rows in order to satisfy a constraint, delete triggers fire if and only if recursive triggers are enabled.
Here’s an example that uses the REPLACE option.
DELETE FROM Products;
INSERT OR REPLACE INTO Products VALUES
(1, 'Hammer', 9.99),
(2, 'Nails', 1.49),
(3, 'Saw', 11.34),
(1, 'Wrench', 37.00),
(5, 'Chisel', 23.00),
(6, 'Bandage', 120.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Wrench 37.0 2 Nails 1.49 3 Saw 11.34 5 Chisel 23.0 6 Bandage 120.0
In this example, the conflict was with the primary key (I tried to insert two rows with the same ProductId). The REPLACE option caused the second one to replace the first.
Rollback
Another option is to use ROLLBACK.
This option aborts the current SQL statement with an SQLITE_CONSTRAINT error and rolls back the current transaction. If no transaction is active (other than the implied transaction that is created on every command) then it works the same as the ABORT algorithm.
Here’s an example that uses multiple INSERT OR ROLLBACK statements within a transaction.
DELETE FROM Products;
BEGIN TRANSACTION;
INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 9.99);
INSERT OR ROLLBACK INTO Products VALUES (2, NULL, 1.49);
INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 11.34);
INSERT OR ROLLBACK INTO Products VALUES (4, 'Wrench', 37.00);
INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00);
INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00);
COMMIT;
SELECT * FROM Products;Here’s the full output from my terminal when I run this:
sqlite> DELETE FROM Products; sqlite> sqlite> BEGIN TRANSACTION; sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 9.99); sqlite> INSERT OR ROLLBACK INTO Products VALUES (2, NULL, 1.49); Error: NOT NULL constraint failed: Products.ProductName sqlite> INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 11.34); sqlite> INSERT OR ROLLBACK INTO Products VALUES (4, 'Wrench', 37.00); sqlite> INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00); sqlite> INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00); sqlite> COMMIT; Error: cannot commit - no transaction is active sqlite> sqlite> SELECT * FROM Products; ProductId ProductName Price ---------- ----------- ---------- 3 Saw 11.34 4 Wrench 37.0 5 Chisel 23.0 6 Bandage 120.0
So it got to the constraint violation, then rolled back the transaction. Then the subsequent lines were processed and then the COMMIT keyword was encountered. By then, the transaction had already been rolled back and so we got another error telling us that no transaction was active.
Here’s what happens if I remove it from the transaction.
DELETE FROM Products;
INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 9.99);
INSERT OR ROLLBACK INTO Products VALUES (2, NULL, 1.49);
INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 11.34);
INSERT OR ROLLBACK INTO Products VALUES (4, 'Wrench', 37.00);
INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00);
INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00);
SELECT * FROM Products;Here’s the full output from my terminal when I run this:
sqlite> DELETE FROM Products; sqlite> sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 9.99); sqlite> INSERT OR ROLLBACK INTO Products VALUES (2, NULL, 1.49); Error: NOT NULL constraint failed: Products.ProductName sqlite> INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 11.34); sqlite> INSERT OR ROLLBACK INTO Products VALUES (4, 'Wrench', 37.00); sqlite> INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00); sqlite> INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00); sqlite> sqlite> SELECT * FROM Products; ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 9.99 3 Saw 11.34 4 Wrench 37.0 5 Chisel 23.0 6 Bandage 120.0
In this case, it worked like ABORT.
To confirm, here’s the same statement using ABORT instead of ROLLBACK.
DELETE FROM Products;
INSERT OR ABORT INTO Products VALUES (1, 'Hammer', 9.99);
INSERT OR ABORT INTO Products VALUES (2, NULL, 1.49);
INSERT OR ABORT INTO Products VALUES (3, 'Saw', 11.34);
INSERT OR ABORT INTO Products VALUES (4, 'Wrench', 37.00);
INSERT OR ABORT INTO Products VALUES (5, 'Chisel', 23.00);
INSERT OR ABORT INTO Products VALUES (6, 'Bandage', 120.00);
SELECT * FROM Products;Here’s the full output from my terminal when I run this:
sqlite> DELETE FROM Products; sqlite> sqlite> INSERT OR ABORT INTO Products VALUES (1, 'Hammer', 9.99); sqlite> INSERT OR ABORT INTO Products VALUES (2, NULL, 1.49); Error: NOT NULL constraint failed: Products.ProductName sqlite> INSERT OR ABORT INTO Products VALUES (3, 'Saw', 11.34); sqlite> INSERT OR ABORT INTO Products VALUES (4, 'Wrench', 37.00); sqlite> INSERT OR ABORT INTO Products VALUES (5, 'Chisel', 23.00); sqlite> INSERT OR ABORT INTO Products VALUES (6, 'Bandage', 120.00); sqlite> sqlite> SELECT * FROM Products; ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 9.99 3 Saw 11.34 4 Wrench 37.0 5 Chisel 23.0 6 Bandage 120.0