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: [email protected] [mailto:sqlite-users- > [email protected]] On Behalf Of Pam Li > Sent: Monday, 16 July, 2012 13:49 > To: [email protected] > 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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
