Category: SQLite

Dealing with Primary Key Conflicts when Inserting Data in SQLite

Posted on by Ian

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.

Continue reading →

Set a Default Value for a Column in SQLite: DEFAULT Constraint

Posted on by Ian

When creating a table in SQLite, you have the option of adding constraints to each column.

One such constraint is the DEFAULT constraint.

The DEFAULT constraint allows you to specify a value to be used in the event no value is supplied for that column when a new row is inserted.

If you don’t use a DEFAULT clause, then the default value for a column is NULL.

Continue reading →

Add a Generated Column to a Table in SQLite

Posted on by Ian

You can add a generated column to an existing table in SQLite by using the ALTER TABLE statement.

SQLite’s implementation of the ALTER TABLE statement is very limited, but it does allow you to add a column – including generated columns.

Generated columns (also known as “computed columns”) are columns that get their value from an expression that computes values from other columns.

Continue reading →

How to Create a Computed Column in SQLite

Posted on by Ian

Generated column support was added to SQLite in version 3.31.0, which was released on 22 January 2020.

Generated columns and computed columns are the same thing. They are columns whose values are a function of other columns in the same row.

In SQLite, generated columns are created using the GENERATED ALWAYS column-constraint when creating or altering the table.

There are two types of generated column; STORED and VIRTUAL. Only VIRTUAL columns can be added when altering a table. Both types can be added when creating a table.

Continue reading →

Convert NULL Values to the Column’s Default Value when Inserting Data in SQLite

Posted on by Ian

One of SQLite‘s non-standard extensions to SQL is the ON CONFLICT clause.

This clause allows you to determine what should happen when certain conflicts occur due to a constraint violation.

One of the things you can use this clause for is to replace NULL values with a column’s default value when inserting or updating data in a table.

By default, if you try to explicitly insert NULL into a column with a NOT NULL constraint, it will fail.

And if you try to explicitly insert NULL into a column without a NOT NULL constraint, then NULL will be assigned to that column, even if there’s a DEFAULT clause.

However, you can use the ON CONFLICT clause to set it to the default value instead of NULL.

Continue reading →

How ON CONFLICT Works in SQLite

Posted on by Ian

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:

  • ABORT
  • FAIL
  • IGNORE
  • REPLACE
  • ROLLBACK

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.

Continue reading →

How to Skip Rows that Violate Constraints When Inserting Data in SQLite

Posted on by Ian

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.

Continue reading →