Insert or ignore into sqlite
The ON CONFLICT clause is a non-standard extension specific to SQLite that can appear in many other SQL commands. It is given its own section in this document because it is not part of standard SQL and therefore might not be familiar.
The ON CONFLICT clause described here has been a part of SQLite since before version 3.0.0 (2004-06-18). The phrase «ON CONFLICT» is also part of UPSERT, which is an extension to INSERT added in version 3.24.0 (2018-06-04). Do not confuse these two separate uses of the «ON CONFLICT» phrase.
The syntax for the ON CONFLICT clause is as shown above for the CREATE TABLE command. For the INSERT and UPDATE commands, the keywords «ON CONFLICT» are replaced by «OR» so that the syntax reads more naturally. For example, instead of «INSERT ON CONFLICT IGNORE» we have «INSERT OR IGNORE». The keywords change but the meaning of the clause is the same either way.
The ON CONFLICT clause applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints. The ON CONFLICT algorithm does not apply to FOREIGN KEY constraints. There are five conflict resolution algorithm choices: ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. The default conflict resolution algorithm is ABORT. This is what they mean:
When an applicable constraint violation occurs, the ROLLBACK resolution algorithm 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 the ROLLBACK resolution algorithm works the same as the ABORT algorithm.
When an applicable constraint violation occurs, the ABORT resolution algorithm 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 behavior and the behavior specified by the SQL standard.
When an applicable constraint violation occurs, the FAIL resolution algorithm aborts the current SQL statement with an SQLITE_CONSTRAINT error. But the FAIL resolution does not back out prior changes of the SQL statement that failed nor does it end the transaction. For example, if an UPDATE statement encountered a constraint violation on the 100th row that it attempts to update, then the first 99 row changes are preserved but changes to rows 100 and beyond never occur.
The FAIL behavior only works for uniqueness, NOT NULL, and CHECK constraints. A foreign key constraint violation causes an ABORT.
When an applicable constraint violation occurs, the IGNORE resolution algorithm 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 the IGNORE conflict resolution algorithm is used. However, the IGNORE conflict resolution algorithm works like ABORT for foreign key constraint errors.
When a UNIQUE or PRIMARY KEY constraint violation occurs, the REPLACE algorithm 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 NULL constraint violation occurs, the REPLACE conflict resolution replaces the NULL value with the default value for that column, or if the column has no default value, then the ABORT algorithm is used. If a CHECK constraint or foreign key constraint violation occurs, the REPLACE conflict resolution algorithm works like ABORT.
When the REPLACE conflict resolution strategy deletes rows in order to satisfy a constraint, delete triggers fire if and only if recursive triggers are enabled.
The update hook is not invoked for rows that are deleted by the REPLACE conflict resolution strategy. Nor does REPLACE increment the change counter. The exceptional behaviors defined in this paragraph might change in a future release.
The algorithm specified in the OR clause of an INSERT or UPDATE overrides any algorithm specified in a CREATE TABLE. If no algorithm is specified anywhere, the ABORT algorithm is used.
- Azure SQL Edge (15)
- Database Concepts (48)
- Database Tools (70)
- DBMS (8)
- MariaDB (417)
- Microsoft Access (17)
- MongoDB (265)
- MySQL (372)
- NoSQL (7)
- Oracle (295)
- PostgreSQL (251)
- Redis (131)
- SQL (587)
- SQL Server (871)
- SQLite (233)
How ON CONFLICT Works in SQLite
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:
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.
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.
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:
Now I’ll insert the same data and use OR IGNORE to skip over the row that violates the constraint.
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.
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 .
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.
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.
Here it is in with separate INSERT statements within a transaction.
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.
The REPLACE option works differently depending on the violation:
- When a UNIQUE or PRIMARY KEY constraint violation occurs,, the REPLACE option 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 NULL constraint violation occurs, it replaces the NULL value with the default value for that column, or if the column has no default value, then the ABORT algorithm is used.
- If a CHECK constraint or foreign key constraint violation occurs, then REPLACE works like ABORT .
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.
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.
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.
Here’s the full output from my terminal when I run this:
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.
Here’s the full output from my terminal when I run this:
In this case, it worked like ABORT .
To confirm, here’s the same statement using ABORT instead of ROLLBACK .
Here’s the full output from my terminal when I run this:
The INSERT statement comes in three basic forms.
The first form (with the «VALUES» keyword) creates one or more new rows in an existing table. If the column-name list after table-name is omitted then the number of values inserted into each row must be the same as the number of columns in the table. In this case the result of evaluating the left-most expression from each term of the VALUES list is inserted into the left-most column of each new row, and so forth for each subsequent expression. If a column-name list is specified, then the number of values in each term of the VALUE list must match the number of specified columns. Each of the named columns of the new row is populated with the results of evaluating the corresponding VALUES expression. Table columns that do not appear in the column list are populated with the default column value (specified as part of the CREATE TABLE statement), or with NULL if no default value is specified.
The second form of the INSERT statement contains a SELECT statement instead of a VALUES clause. A new entry is inserted into the table for each row of data returned by executing the SELECT statement. If a column-list is specified, the number of columns in the result of the SELECT must be the same as the number of items in the column-list. Otherwise, if no column-list is specified, the number of columns in the result of the SELECT must be the same as the number of columns in the table. Any SELECT statement, including compound SELECTs and SELECT statements with ORDER BY and/or LIMIT clauses, may be used in an INSERT statement of this form.
To avoid a parsing ambiguity, the SELECT statement should always contain a WHERE clause, even if that clause is simply «WHERE true», if the upsert-clause is present. Without the WHERE clause, the parser does not know if the token «ON» is part of a join constraint on the SELECT, or the beginning of the upsert-clause.
INSERT INTO table DEFAULT VALUES;
The third form of an INSERT statement is with DEFAULT VALUES. The INSERT . DEFAULT VALUES statement inserts a single new row into the named table. Each column of the new row is populated with its default value, or with a NULL if no default value is specified as part of the column definition in the CREATE TABLE statement. The upsert-clause is not supported after DEFAULT VALUES.
The initial «INSERT» keyword can be replaced by «REPLACE» or «INSERT OR action» to specify an alternative constraint conflict resolution algorithm to use during that one INSERT command. For compatibility with MySQL, the parser allows the use of the single keyword REPLACE as an alias for «INSERT OR REPLACE».
The optional «schema-name.» prefix on the table-name is supported for top-level INSERT statements only. The table name must be unqualified for INSERT statements that occur within CREATE TRIGGER statements. Similarly, the «DEFAULT VALUES» form of the INSERT statement is supported for top-level INSERT statements only and not for INSERT statements within triggers.
The optional «AS alias » phrase provides an alternative name for the table into which content is being inserted. The alias name can be used within WHERE and SET clauses of the UPSERT. If there is no upsert-clause, then the alias is pointless, but also harmless.
See the separate UPSERT documentation for the additional trailing syntax that can cause an INSERT to behave as an UPDATE if the INSERT would otherwise violate a uniqueness constraint. The upsert clause is not allowed on an «INSERT . DEFAULT VALUES».
SQLite insert, update, delete data
last modified July 6, 2020
In this part of the SQLite tutorial, we will insert, update and delete data from SQLite tables. We will use the INSERT , DELETE , and UPDATE statements. These statements are part of the SQL Data Manipulation Language (DML).
SQLite insert data
The INSERT statement is used to insert data into tables. We will create a new table in which to execute our examples.
We create a new table Cars with Id , Name , and Price columns.
This is the classic INSERT statement. We have specified all column names after the table name and all values after the VALUES keyword. The first row is added into the table.
We add a new car into the Cars table. We have omitted the Id column. The Id column is defined as INTEGER PRIMARY KEY and such columns are auto-incremented in SQLite. This means the SQLite library will add a new Id itself.
Here is what we have in the Cars table at the moment.
In this SQL statement, we did not specify any column names after the table name. In such a case, we have to supply all values.
The .nullvalue command tells the SQLite to show NULL values as NULL . SQLite shows empty strings for NULL values by default.
The INSERT statement omits the last 2 columns. Such columns are filled with the default value or NULL if there is no default value. The Name column does not have a default value, so there is a NULL value. In the CREATE TABLE statement, we have specified the Price column to have the ‘Not available’ default value.
In the second column we have a NULL value. The third has the default ‘Not available’ string.
Say we want to put all information into the fourth column. Trying to insert new data into existing row produces the following error: UNIQUE constraint failed: Cars.Id.
In such a case we can use the INSERT OR REPLACE statement. The same could be accomplished with the UPDATE statement.
Now we have all information in the fourth row.
The INSET OR FAIL INTO statement is equal to the INSERT INTO statement. It is just a bit more specific that it fails in case of an error.
The INSERT OR IGNORE INTO statement ignores the error message. The SELECT statement shows that the last two statements did not modify the fourth row.
Since SQLite version 3.7.11 it is possible to insert multiple rows using one INSERT statement.
We will use a one-column Ints table to show a multi-row INSERT statement. The table’s lone column stores integers.
We insert nine rows into the table in one shot. The rows follow the VALUES keyword and are separated by a comma character.
These are the contents of the Ints table.
We can use the INSERT and SELECT statements together in one statement.
First, we create a new table called Cars2 .
Here we insert all data from the Cars table into the Cars2 table.
SQLite delete data
The DELETE keyword is used to delete data from tables. First, we are going to delete one row from a table. We will use the Cars2 table which we have created previously.
We delete a row with Id 1.
We verify that the first row is missing.
This SQL statement deletes all data in the table.
This SQL statement confirms that there are no rows in the Cars2 table now.
With the .read meta command, we create a new Cars table. (The SQL for the table can be found in the first chapter of this tutorial.)
LIMIT clause, it is possible to restrict the number of deleted rows. Five rows were deleted and three rows are left.
SQLite update data
The UPDATE statement is used to modify a subset of the values stored in zero or more rows of a database table.
Say we wanted to change ‘Skoda’ to ‘Skoda Octavia’ in our Cars table. The following statement shows how to accomplish this:
The SQL statement sets the name of a car to ‘Skoda Octavia’ for the column with Id=3 .
The row is correctly updated.
In this part of the SQLite tutorial, we have inserted, deleted, and updated data in database tables.