复合主键的自动递增 - Sqlite3 + Python
我有一个这样的代码
c.execute('CREATE TABLE IF NOT EXISTS base (ID INTEGER NOT NULL, col2 TEXT NOT NULL, col3 INTEGER, PRIMARY KEY(ID, col2))')
这段代码给了我一个 sqlite3.IntegrityError 异常，尽管我很确定我是第一次写记录.
This code gives me an sqlite3.IntegrityError exception even though I am very sure that I am writing the record for the first time.
所以，我试过了
c.execute('CREATE TABLE IF NOT EXISTS base (ID INTEGER, col2 TEXT, col3 INTEGER, PRIMARY KEY(ID, col2))')
这会在 base 表中插入行，但 ID 列根本不会自动递增.
This inserts the row fine in the base table BUT, ID column is not auto incremented at all.
我能做什么?有什么想法吗?
What can I do? Any idea?
在 sqlite 中，只有当只有一个整数列是主键时，您才会获得自动增量行为.复合键阻止自增生效.
In sqlite, you only get autoincrement behavior when only one integer column is the primary key. composite keys prevent autoincrement from taking effect.
您可以通过将 id 定义为唯一的主键，然后在 id, col3 上添加额外的唯一约束来获得类似的结果.
You can get a similar result by defining id as the only primary key, but then adding an additional unique constraint on id, col3.
如果这仍然不是您想要的(例如，id 根本不需要是唯一的)，您可能必须使用触发器来使自动增量工作.
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.