SQLITE basic syntax

Question

I seem to misunderstand a basic syntax, why this sample works:

sqlite3_prepare_v2(db, "insert into test values('boo','boo',0);", strlen(querystring)+1 , &stmt, NULL);

if ((rc = sqlite3_step(stmt)) != SQLITE_DONE)
  fprintf(stderr, "Error: sqlite3_step() %d. Error Message %s;\n",rc,sqlite3_errmsg(db));

But when I try this query: "insert into test(strtest) values('boo');"

I get an error: Error: sqlite3_step() 19. Error Message constraint failed;

What am I missing?

table test is: "create table test (blobtest BLOB(4) NOT NULL, strtest VARCHAR NOT NULL, inttest INTEGER NOT NULL );"

Thanks, Doori Bar

Answer 1

All three columns are declared as NOT NULL, and they do not have a default value.

So your query:

insert into test(strtest) values('boo');

Is only assigning blobtest a value, leaving out strtest and inttest which cannot be null.

You can fix this by either adding two more values, or you can change the table schema to default to something, e.g.:

create table test (
    blobtest BLOB(4) NOT NULL,
    strtest VARCHAR NOT NULL DEFAULT '',
    inttest INTEGER NOT NULL DEFAULT 0
);

(or you could take out NOT NULL if you would like them to be NULL-able)