CODEWITHSUNDEEP
pythonsqlite
yayu
yayu

Reputation: 8088

No autoincrement for Integer Primary key in sqlite3

In the sqlite3 faq, 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?

Upvotes: 27

Views: 19358

Answers (3)

jaime sommer
jaime sommer

Reputation: 1

you should write as follow

INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ======================

for example:

cx01_registro = 0               # pic 9(06)
cx01_codigolancamento = ""      # pic x(02)
cx01_descricaolancamento = ""   # pic x(15)
cx01_tipolancamento = ""        # pic x(01) <+> deposito <-> retirada
conn = sqlite3.connect('caixa.db')
c = conn.cursor()
c.execute("CREATE TABLE IF NOT EXISTS TABCOD"
          "(cx01_registro INTEGER PRIMARY KEY  AUTOINCREMENT NOT NULL,"
           "cx01_codigolancamento CHAR(02),"
           "cx01_descricaolancamento CHAR(15),"
           "cx01_tipolancamento CHAR(01))")
conn.close()
#///////////////////////////
conn = sqlite3.connect('caixa.db')
print("Opened database successfully")
conn.execute("INSERT INTO TABCOD (cx01_registro,cx01_codigolancamento,"
             "cx01_descricaolancamento,cx01_tipolancamento) \
             VALUES (NULL, 'dp', 'deposito', '+')")
conn.execute("INSERT INTO TABCOD (cx01_registro,cx01_codigolancamento,"
             "cx01_descricaolancamento,cx01_tipolancamento) \
             VALUES (NULL, 'rt', 'retirada', '-')")
conn.commit()

this way will work

if you write as follows

INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT ======================
it will not work.

Upvotes: 0

Shankari
Shankari

Reputation: 399

The insert syntax provided above does not seem to work in the absence of not null.

Here's an example - note that the ID field is not autoincremented even though I use the insert format that you specified above.

sqlite> .schema logTable
CREATE TABLE logTable (ID INTEGER PRIMARY_KEY, ts REAL, level TEXT, message TEXT);
sqlite> INSERT into LOGTABLE (ts, level, message) VALUES (111, "autoinc test", "autoinc test");
sqlite> select * from logtable where ts = 111;
|111.0|autoinc test|autoinc test
sqlite>

It does work with the NOT NULL workaround.

sqlite> create TABLE logTable (ID INTEGER PRIMARY KEY NOT NULL, ts REAL, level TEXT, message TEXT);
sqlite> INSERT into LOGTABLE (ts, level, message) VALUES (222, "autoinc test", "autoinc test"); 
sqlite> select * from logtable where ts = 222;
1|222.0|autoinc test|autoinc test

I apologize for posting this as a new answer instead of commenting on the previous answer, but my reputation score is too low to add comments, and I thought that it was important to note that the alternate insert statement is not an adequate workaround.

Upvotes: 3

mu is too short
mu is too short

Reputation: 434585

This is one of SQLite's quirks. From the fine manual:

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 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".

Upvotes: 34

Related Questions