If what you really want is for all values to be -1000 in table AA when there is either no value provided or an attempt is made to insert / update with a NULL, then the following will achieve that, through triggers.
sqlite> CREATE TABLE AA (column1 INTEGER, ...> column2 INTEGER NOT NULL, ...> column3 REAL NOT NULL, ...> column4 INTEGER DEFAULT -1000, ...> PRIMARY KEY(column1)); sqlite> create trigger aa_insert after insert on aa for each row when NEW.column4 is null ...> begin ...> update aa set column4=-1000; ...> end; sqlite> create trigger aa_update after update on aa for each row when NEW.column4 is null ...> begin ...> update aa set column4=-1000; ...> end; sqlite> insert into aa values (1,2,3,NULL); sqlite> insert into aa (column1, column2, column3) values (2,3, 4); sqlite> select * from aa; 1|2|3.0|-1000 2|3|4.0|-1000 The triggers will set the "default" whenever you try to insert or update a NULL into column4. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Keith Medcalf > Sent: Monday, 16 July, 2012 14:15 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Table Filed Not Allowed to Be NULL? > > > sqlite> CREATE TABLE AA (column1 INTEGER, > ...> column2 INTEGER NOT NULL, > ...> column3 REAL NOT NULL, > ...> column4 INTEGER NOT NULL DEFAULT -1000, > ...> PRIMARY KEY(column1)); > > sqlite> insert into aa values (1,2,3); > Error: table aa has 4 columns but 3 values were supplied > > sqlite> insert into aa values (1,2,3,); > Error: near ")": syntax error > > sqlite> insert into aa values (1,2,3,NULL); > Error: AA.column4 may not be NULL > > sqlite> insert into aa (column1, column2, column3) values (2,3, 4); > > sqlite> select * from aa; > 2|3|4.0|-1000 > > Works as expected ... The default value is only used when you do not provide > a value. Not when you provide a "bad" value (such as a NULL that is not > allowed by the column constraints). > > .import cannot import using defaults since it expects to contain a value on > each row for every column. > > --- > () ascii ribbon campaign against html e-mail > /\ www.asciiribbon.org > > > > -----Original Message----- > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > boun...@sqlite.org] On Behalf Of Pam Li > > Sent: Monday, 16 July, 2012 13:49 > > To: sqlite-users@sqlite.org > > Subject: [sqlite] Table Filed Not Allowed to Be NULL? > > > > > > > > > > > > Hi, > > > > In the table created below: CREATE TABLE > > AA (column1 INTEGER, > > > > column2 INTEGER NOT NULL, > > > > column3 REAL NOT > > NULL, > > > > column4 > > INTEGER NOT NULL DEFAULT -1000, > > > > PRIMARY > > KEY(column1)) I tried adding rows manually using sqlite3 command shell. > It > > does not take a row without inputing column4. It seems default value (- > 1000) > > does not take effect. When forced to input a row without column 4 (by using > > .import <file> <table> in sqlite3 shell), the value for column4 got back > > from the db is always 0. Am I missing anything? It seems that I am not > able > > to make the default value work for a "NOT NULL" table field. Thanks a > > lot,pam > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
