CODEWITHSUNDEEP
sqliteinsertnulldefault-value
Pittfall
Pittfall

Reputation: 2851

SQLite default value if null

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?

Upvotes: 7

Views: 11039

Answers (4)

anadam92
anadam92

Reputation: 21

  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/

  1. 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

Upvotes: 2

sffc
sffc

Reputation: 6414

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.

Upvotes: 1

benni_mac_b
benni_mac_b

Reputation: 8877

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

Upvotes: 1

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

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.

Upvotes: 2

Related Questions