SQLITE INSERT DUPLICATE TO UNIQUE HOW TO
SQLITE INSERT DUPLICATE TO UNIQUE UPDATE
SQLITE INSERT DUPLICATE TO UNIQUE FULL

Sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50) Įrror: UNIQUE constraint failed: Products.ProductId Sqlite> INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50) Sqlite> INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50) Sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 8.00)

SQLITE INSERT DUPLICATE TO UNIQUE FULL

Here’s the full output from my terminal when I run this: sqlite> BEGIN TRANSACTION INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00) INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00) INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50) INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50) INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50) INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 8.00) Here’s an example that uses multiple INSERT OR ROLLBACK statements within a transaction. It pays to be mindful of how this option works. 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. This aborts the current SQL statement with an SQLITE_CONSTRAINT error and rolls back the current transaction. Example 4 – RollbackĪnother option is to use the ROLLBACK option.

SQLITE INSERT DUPLICATE TO UNIQUE UPDATE

So the effect is kind of like an UPDATE statement and INSERT statement combined. We can also see that it used the second set of values (seeing as two shared the same ProductId). However, we can see that the first row has been updated to use the values in my INSERT statement. In this case most rows were the same, so they contain the same data after the INSERT operation. In other words, you will overwrite the existing data with your new data. Example 3 – ReplaceĪnother option you have is to replace the original row with the new row. 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.

The effect of this is that the INSERT operation succeeds, but without any rows that violate the primary key constraint. To do this within your INSERT statement, use OR IGNORE. In other words, it will skip over the row and continue processing subsequent rows. One alternative is to have SQLite ignore the offending row. We can see that the table only contains the original row. We can verify that nothing was inserted by running a SELECT statement against the table. INSERT OR ABORT INTO Products VALUES (1, 'Wrench', 12.50)

This is the equivalent of using the OR ABORT option.

Result: Error: UNIQUE constraint failed: Products.ProductIdĪn error was returned and nothing was inserted. INSERT INTO Products VALUES (1, 'Wrench', 12.50) Example 1 – Abort (Default Behaviour)Īs mentioned, the default behaviour for SQLite is to abort the INSERT operation and return an error. Now we can run through the various scenarios of inserting data into that table that violates the primary key constraint. We currently have one row, with a ProductId of 1. INSERT INTO Products VALUES (1, 'Hammer', 8.00) Let’s create a simple table and add one row. 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.

When you use this option, the syntax is different you use OR instead of ON CONFLICT. This allows you to take advantage of the clause even when the table wasn’t created with it. Doing that will determine how all INSERT operations are treated.Īnother option is to use the clause on the INSERT statement whenever you try to insert data into the table. One option is to use this clause in the CREATE TABLE statement when creating the table. By default, when you try to do this, the operation will be aborted and SQLite will return an error.īut you can use the ON CONFLICT clause to change the way SQLite deals with these situations.

SQLITE INSERT DUPLICATE TO UNIQUE HOW TO

This article provides examples of how this clause can be used to determine how to handle primary key constraint conflicts.īy “primary key constraint conflicts”, I mean when you try to insert a duplicate value into a primary key column. In particular, the clause applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints. SQLite has a non-standard SQL extension clause called ON CONFLICT that enables us to specify how to deal with constraint conflicts.

Sqlite insert duplicate to unique