SQLite default value if null

Question

Let's say I have a table called "table"

So

Create Table "Table" (a int not null, b int default value 1)

If I do a "INSERT INTO "Table" (a) values (1)". I will get back 1 for column a and 1 for column b as the default value for column b is 1.

BUT if I do "INSERT INTO "Table" (a, b) values (1, null)". I will bet back 1 for column a and an empty value for column b. Is there a way to set a column's default value if a null was given?

Gaytan

Answer 1

No, if you are doing:

INSERT INTO my_table (a, b) values (1, null) 

You are explicitely asking for a null value on b column.

In a RDBMS you could technically use a trigger to override that behavior. But in SQLite you can't.

Dilan

Answer 2

1. If the column with the default value can have the NOT NULL constraint, then you can:

   • create the table using this syntax:

     CREATE TABLE "Table" (a INT NOT NULL, b INT NOT NULL ON CONFLICT REPLACE DEFAULT 1);

     and insert as usual

   • create the table as in your question

     and insert using this syntax:

     INSERT OR REPLACE INTO "Table" (a, b) VALUES(1, null);

https://database.guide/convert-null-values-to-the-columns-default-value-when-inserting-data-in-sqlite/

2. If the column with the default value can not have the NOT NULL constraint (allowing NULL to be inserted), as in your question:

   you will have to omit the column with the default value from the insert query so that it gets its default.

Ideal would be:

INSERT INTO "Table" (a, b) VALUES(1, COALESCE(NULL, DEFAULT))

, as is in other sql dialects, which might be supported in future release: https://sqlite.org/forum/info/d7384e085b808b05

Hirsh

Answer 3

If you don't want nulls for column b then you should set it as a non null-able field as you have done with a

Afrikaans

Answer 4

This solution for MySQL should mostly work for SQLite. The main, and albeit major, difference is that there doesn't seem to be a Default() function in SQLite like there is in MySQL. I was able to replicate this functionality by keeping track of my database schema in PHP and then manually inserting the default value as the second argument to Coalesce(). See this Gist for example code.

Calabro