No autoincrement for Integer Primary key in sqlite3
Question: In the sqlite3 [faq](http://www.sqlite.org/faq.html#q1), it is mentioned that
an integer primary key being fed a null value would autoincrement. But this is
not happening for me.

to replicate, a table in sqlite3, `CREATE TABLE dummy( serial_num INTEGER
PRIMARY KEY, name TEXT);` and fill it using python,

    
    
    import sqlite3 as lite
    con = lite.connect('some.db')
    cur=con.cursor()
    data = "someone's name"
    cur.execute("INSERT INTO dummy VALUES(NULL, ?)", data)
    con.commit()
    

The first attribute serial_num is being shown blank while the name attribute
is fine. When I do `SELECT serial_num FROM dummy` I just get a bunch of blank
spaces. What am I doing wrong?


Answer: This is one of SQLite's quirks. From the [fine
manual](http://www.sqlite.org/lang_createtable.html#primkeyconst):

> According to the SQL standard, PRIMARY KEY should always imply NOT NULL.
> Unfortunately, due to a long-standing coding oversight, this is not the case
> in SQLite. Unless the column is an INTEGER PRIMARY KEY SQLite allows NULL
> values in a PRIMARY KEY column. We could change SQLite to conform to the
> standard (and we might do so in the future), but by the time the oversight
> was discovered, SQLite was in such wide use that we feared breaking legacy
> code if we fixed the problem.

The documentation on [INTEGER PRIMARY
KEY](http://www.sqlite.org/lang_createtable.html#rowid) is a little unclear
about what precisely is required for a column to be this special INTEGER
PRIMARY KEY that auto-increments but the reality is that the column needs to
be NOT NULL if you want to use the NULL value to mean "give me the next auto-
incrementing value" when inserting:

    
    
    create table dummy (
        serial_num integer primary key not null,
        name text
    );
    

If you leave out the `not null`, you need to do your inserts like this:

    
    
    insert into dummy (name) values (?)
    

to get the auto-increment value for `serial_num`. Otherwise, SQLite has no way
of telling the difference between a NULL meaning "give me the next auto-
increment value" and a NULL meaning "put a NULL value in `serial_num` because
the column allows NULLs".

