How to INSERT OR IGNORE in SQLite

Sometimes when we try to INSERT multiple data in our database simultaneously, we get a conflict error. But here is a way to avoid this error and continue executing other SQL statements.

This article will show how we can avoid conflict when inserting data in SQLite. Also, this article contains some examples and explanations to make the topic easier.

For this purpose, we will use a built-in command in SQLite known as IGNORE. The keyword IGNORE is a resolution algorithm that skips a row when the row contains the constraint violation.

But it doesn’t stop executing the other commands; it continues to execute other SQL statements. This helps us avoid conflict when trying to INSERT multiple data at a time in a database.

Besides the keyword, IGNORE mostly works like the ABORT keyword for foreign key constraint errors.

Use the INSERT OR IGNORE in SQLite

Now take a look at our first query. This SQL query inserts some data into the table of a database.

But if the data already exists or conflicts with any other data in the table, it will show us an error.

INSERT INTO demo VALUES (50, 'Alen', 'Sample data', '2022-05-12');

But we can avoid this situation by using the keyword IGNORE. Now let’s take a look at our next query.

INSERT OR IGNORE INTO demo VALUES (50, 'Alen', 'Sample data', '2022-05-12');

Here we used the keyword IGNORE with the keyword INSERT. Now, if the data set (50, 'Alen', 'Sample data', '2022-05-12') conflicts with any other table data, the query doesn’t stop executing or show any error.

It just executes smoothly and doesn’t affect the database.

Please note that the queries we provided in this article are written for SQLite.