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