How to add default value in SQLite?

Question

I had a table modified to add status column to it in this fashion

ALTER TABLE ITEM ADD COLUMN STATUS VARCHAR DEFAULT 'N'; 

However SQLite doesnt seem to add N to the column for any new ITEM created. Is the syntax wrong or is there any issue with SQLite and its support for defaults.

I am using SQLite 3.6.22

Answer 1

Looks good to me. Here are the Docs.

sqlite> create table t1 (id INTEGER PRIMARY KEY, name TEXT, created DATE); sqlite> .table t1 sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE t1 (id INTEGER PRIMARY KEY, name TEXT, created DATE); COMMIT;  sqlite> alter table t1 add column status varchar default 'N'; sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE t1 (id INTEGER PRIMARY KEY, name TEXT, created DATE, status varchar default 'N'); COMMIT;  sqlite> insert into t1 (name) values ("test"); sqlite> select * from t1; 1|test||N 

Dump your schema and verify that your table structure is there after calling ALTER TABLE but before the INSERT. If it's in a transaction, make sure to COMMIT the transaction before the insert.

$ sqlite3 test.db ".dump"