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.

Example

The following code demonstrates what I mean.

DROP TABLE IF EXISTS Products;

CREATE TABLE Products( 
    ProductId INTEGER PRIMARY KEY, 
    ProductName NOT NULL, 
    Price NOT NULL ON CONFLICT REPLACE DEFAULT 0.00
);

INSERT INTO Products (ProductId, ProductName, Price) VALUES 
    (1, 'Widget Holder', NULL);

SELECT * FROM Products;

In this example I use ON CONFLICT REPLACE to set NULL values to the default value instead of NULL.

Here’s the result from the SELECT statement on the last line:

ProductId   ProductName    Price     
----------  -------------  ----------
1           Widget Holder  0.0       

We can see that the Price column has the default value of 0.0 even though I tried to explicitly insert NULL.

Let’s see what happens if I remove the NOT NULL constraint.

DROP TABLE IF EXISTS Products;

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

INSERT INTO Products (ProductId, ProductName, Price) VALUES 
    (1, 'Widget Holder', NULL);

SELECT * FROM Products;

Result:

ProductId   ProductName    Price     
----------  -------------  ----------
1           Widget Holder            

Now the column contains NULL.

Inserting NULL Implicitly

It’s important to note that this article is mainly concerned with inserting NULL explicitly.

If you try to insert NULL implicitly, then the previous example will produce a different result.

What I mean is, if you don’t include the column in the INSERT statement, the DEFAULT constraint will be used automatically. That’s what DEFAULT constraints are for – to provide a value when you don’t explicitly provide one.

Here’s what happens when I do that.

DROP TABLE IF EXISTS Products;

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

INSERT INTO Products (ProductId, ProductName) VALUES 
    (1, 'Widget Holder');

SELECT * FROM Products;

Result:

ProductId   ProductName    Price     
----------  -------------  ----------
1           Widget Holder  0.0       

So all I did was remove the Price column from the INSERT statement.

ON CONFLICT for the INSERT Statement

The first example uses ON CONFLICT on the CREATE TABLE statement.

But what if the table wasn’t created with the ON CONFLICT clause?

Fortunately, there’s also a way to use it on the INSERT statement.

The syntax is slightly different. When used on the INSERT statement you need to replace ON CONFLICT with OR.

Let’s modify the code to use this method.

DROP TABLE IF EXISTS Products;

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

INSERT OR REPLACE INTO Products (ProductId, ProductName, Price) VALUES 
    (1, 'Widget Holder', NULL);

SELECT * FROM Products;

Result:

ProductId   ProductName    Price     
----------  -------------  ----------
1           Widget Holder  0.0       

So I replaced INSERT INTO with INSERT OR REPLACE INTO.

Here’s what the result would be had I not put that clause in.

DROP TABLE IF EXISTS Products;

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

INSERT INTO Products (ProductId, ProductName, Price) VALUES 
    (1, 'Widget Holder', NULL);

SELECT * FROM Products;

Result:

Error: NOT NULL constraint failed: Products.Price

No DEFAULT Constraint?

In the event that you use the ON CONFLICT clause on a column without a DEFAULT constraint, the SQL statement is aborted with an SQLITE_CONSTRAINT error any changes made by the current SQL statement are backed out; but changes caused by prior SQL statements within the same transaction are preserved and the transaction remains active.