SQLite has a non-standard SQL extension clause called ON CONFLICT that enables us to specify how to deal with constraint conflicts.
In particular, the clause applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints.
This article provides examples of how this clause can be used to determine how to handle primary key constraint conflicts.
By “primary key constraint conflicts”, I mean when you try to insert a duplicate value into a primary key column. By default, when you try to do this, the operation will be aborted and SQLite will return an error.
But you can use the ON CONFLICT clause to change the way SQLite deals with these situations.
One option is to use this clause in the CREATE TABLE statement when creating the table. Doing that will determine how all INSERT operations are treated.
Another option is to use the clause on the INSERT statement whenever you try to insert data into the table. This allows you to take advantage of the clause even when the table wasn’t created with it. When you use this option, the syntax is different; you use OR instead of ON CONFLICT.
The examples on this page use the second option – I create the table without the ON CONFLICT clause, and I instead specify OR on the INSERT statement.
Sample Table
Let’s create a simple table and add one row.
CREATE TABLE Products(
ProductId INTEGER PRIMARY KEY,
ProductName,
Price
);
INSERT INTO Products VALUES (1, 'Hammer', 8.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0
We currently have one row, with a ProductId of 1.
Now we can run through the various scenarios of inserting data into that table that violates the primary key constraint.
Example 1 – Abort (Default Behaviour)
As mentioned, the default behaviour for SQLite is to abort the INSERT operation and return an error.
INSERT INTO Products VALUES (1, 'Wrench', 12.50);Result:
Error: UNIQUE constraint failed: Products.ProductId
An error was returned and nothing was inserted.
This is the equivalent of using the OR ABORT option.
INSERT OR ABORT INTO Products VALUES (1, 'Wrench', 12.50);Result:
Error: UNIQUE constraint failed: Products.ProductId
We can verify that nothing was inserted by running a SELECT statement against the table.
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0
We can see that the table only contains the original row.
Example 2 – Ignore
One alternative is to have SQLite ignore the offending row. In other words, it will skip over the row and continue processing subsequent rows.
To do this within your INSERT statement, use OR IGNORE.
The effect of this is that the INSERT operation succeeds, but without any rows that violate the primary key constraint.
INSERT OR IGNORE INTO Products VALUES
(1, 'Hammer', 12.00),
(2, 'Nails', 2.50),
(3, 'Saw', 10.50),
(1, 'Wrench', 22.50),
(5, 'Chisel', 23.00),
(6, 'Bandage', 120.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0 2 Nails 2.5 3 Saw 10.5 5 Chisel 23.0 6 Bandage 120.0
In this case I tried to insert two new rows with an ID that already existed in the table, so both of those rows were skipped.
Example 3 – Replace
Another option you have is to replace the original row with the new row.
In other words, you will overwrite the existing data with your new data.
To do this, use OR REPLACE.
INSERT OR REPLACE INTO Products VALUES
(1, 'Hammer', 12.00),
(2, 'Nails', 2.50),
(3, 'Saw', 10.50),
(1, 'Wrench', 22.50),
(5, 'Chisel', 23.00),
(6, 'Bandage', 120.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Wrench 22.5 2 Nails 2.5 3 Saw 10.5 5 Chisel 23.0 6 Bandage 120.0
In this case most rows were the same, so they contain the same data after the INSERT operation. However, we can see that the first row has been updated to use the values in my INSERT statement.
We can also see that it used the second set of values (seeing as two shared the same ProductId).
So the effect is kind of like an UPDATE statement and INSERT statement combined.
Example 4 – Rollback
Another option is to use the ROLLBACK option.
This 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.
It pays to be mindful of how this option works. 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', 8.00);
INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50);
INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50);
INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50);
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> BEGIN TRANSACTION; sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 8.00); sqlite> INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50); sqlite> INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50); sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50); Error: UNIQUE constraint failed: Products.ProductId 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 ---------- ----------- ---------- 5 Chisel 23.0 6 Bandage 120.0 sqlite>
Basically what here happened is that, it has gotten as far as the constraint violation, then rolled back the transaction. Then the next two 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', 8.00);
INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50);
INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50);
INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50);
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', 8.00); sqlite> INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50); sqlite> INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50); sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50); Error: UNIQUE constraint failed: Products.ProductId 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 8.0 2 Nails 2.5 3 Saw 10.5 5 Chisel 23.0 6 Bandage 120.0 sqlite>
In this case, it worked like ABORT.
To demonstrate this, here’s the same statement using ABORT instead of ROLLBACK.
DELETE FROM Products;
INSERT OR ABORT INTO Products VALUES (1, 'Hammer', 8.00);
INSERT OR ABORT INTO Products VALUES (2, 'Nails', 2.50);
INSERT OR ABORT INTO Products VALUES (3, 'Saw', 10.50);
INSERT OR ABORT INTO Products VALUES (1, 'Wrench', 22.50);
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', 8.00); sqlite> INSERT OR ABORT INTO Products VALUES (2, 'Nails', 2.50); sqlite> INSERT OR ABORT INTO Products VALUES (3, 'Saw', 10.50); sqlite> INSERT OR ABORT INTO Products VALUES (1, 'Wrench', 22.50); Error: UNIQUE constraint failed: Products.ProductId 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 8.0 2 Nails 2.5 3 Saw 10.5 5 Chisel 23.0 6 Bandage 120.0 sqlite>
The Fail Option
The FAIL option aborts the current SQL statement with an SQLITE_CONSTRAINT error. But this option does not back out prior changes of the SQL statement that failed nor does it end the transaction.
DELETE FROM Products;
INSERT OR FAIL INTO Products VALUES
(1, 'Hammer', 8.00),
(2, 'Nails', 2.50),
(3, 'Saw', 10.50),
(1, 'Wrench', 22.50),
(5, 'Chisel', 23.00),
(6, 'Bandage', 120.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0 2 Nails 2.5 3 Saw 10.5