Auto Increment on Composite Primary Key - Sqlite3 + Python

Question

I have a code like this

c.execute('CREATE TABLE IF NOT EXISTS base (ID INTEGER NOT NULL, col2 TEXT NOT NULL, col3 INTEGER, PRIMARY KEY(ID, col2))')

This code gives me an sqlite3.IntegrityError exception even though I am very sure that I am writing the record for the first time.

So, I tried

c.execute('CREATE TABLE IF NOT EXISTS base (ID INTEGER, col2 TEXT, col3 INTEGER, PRIMARY KEY(ID, col2))')

This inserts the row fine in the base table BUT, ID column is not auto incremented at all.

What can I do? Any idea?

Answer 1

In sqlite, you only get autoincrement behavior when only one integer column is the primary key. composite keys prevent autoincrement from taking effect.

You can get a similar result by defining id as the only primary key, but then adding an additional unique constraint on id, col3.

If that's still not quite what you want (say, id's don't need to be unique at all), you probably will have to use a trigger to make autoincrement work.