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

What version of sqlite are you using? I am using 3.6.16 and the sql you provided worked perfectly for me. — Jansen Price
– Jansen Price, Commented Feb 12, 2010 at 19:27
Maybe it is a problem with a frontend you are using to manipulate the db. Have you tried the non-functional insert to execute "by hand" (in sqlite cli client)? — Messa
– Messa, Commented Feb 12, 2010 at 19:34
It seems it was actually working , the problem was with frontend using to manipulate the DB — Azlam
– Azlam, Commented Feb 12, 2010 at 19:45

databyte · Accepted Answer · 2010-02-12 19:37:49Z

65

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"

answered Feb 12, 2010 at 19:37

databyte

1,26810 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add default value in SQLite?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related