In SQLite, when you try to insert multiple rows into a table, and any of those rows violates a constraint on that table, the operation will fail.

This is to be expected, after all, that’s what the constraint is for.

But what if you just want to ignore any rows that violate constraints? In other words, if a row violates a constraint, you want SQLite to skip that row, then carry on processing the next row, and so on.

Fortunately, there’s an easy way to do this in SQLite.

The ON CONFLICT Clause

SQLite has the ON CONFLICT clause that allows you to specify how to handle constraint conflicts. More specifically, it applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints (but not FOREIGN KEY constraints).

The ON CONFLICT clause is used in CREATE TABLE statements, but when inserting data, the clause is replaced with OR.

Therefore, you can use this clause to determine how to handle constraint violations when inserting data.

There are five possible values you can use with this clause:

• ROLLBACK
• ABORT
• FAIL
• IGNORE
• REPLACE

For the purposes of this article, we’ll be using the IGNORE option.

Using IGNORE causes SQLite to skip the one row that contains the constraint violation and continue processing subsequent rows as if nothing went wrong.

Example

Here’s a CREATE TABLE statement for a table called Products:

CREATE TABLE Products( 
    ProductId INTEGER PRIMARY KEY, 
    ProductName NOT NULL, 
    Price 
);

Notice that this table includes a NOT NULL constraint on the ProductName column.

Now let’s try to insert data that violates that constraint.

INSERT INTO Products VALUES 
    (1, 'Widget Holder', 139.50),
    (2, NULL, 11.00),
    (3, 'Widget Stick', 89.75);

Result:

Error: NOT NULL constraint failed: Products.ProductName

Not surprisingly, we get an error indicating that the NOT NULL constraint was violated.

Now let’s see how many rows were inserted into the table.

SELECT COUNT(*) FROM Products;

Result:

0

So we know that only the second row violated the constraint, but this prevented any data from being inserted.

We can change this by adding OR IGNORE to our INSERT statement:

INSERT OR IGNORE INTO Products VALUES 
    (1, 'Widget Holder', 139.50),
    (2, NULL, 11.00),
    (3, 'Widget Stick', 89.75);

That’s all that’s required. Running this code does not result in an error like the previous code. Running this code results in the good data being inserted, and the bad data being ignored.

Now if we run a SELECT statement against the table, we can see that the good data was in fact inserted.

SELECT * FROM Products;

Result:

ProductId   ProductName    Price     
----------  -------------  ----------
1           Widget Holder  139.5     
3           Widget Stick   89.75