Sqlite: multi-column primary key with auto-enlarge column

Question

Basically I want to convert a table from mysql to sqlite with the following schema:

create table items ( id integer auto_increment, version integer default 0, primary key (id, version) ); 

Essentially, I want the identifier to automatically increment every time I insert something into the table, with VERSION starting at 0, but still allowing multiple elements with the same identifier, while VERSION is different.

I am trying to replicate this behavior using Sqlite, but I can not get to work with tables. It seems that you are only allowed one column as an auto-increment, and it should be the main key. If I make the primary key identifier, then I cannot use VERSION as part of the key. However, if I create a multi-column key (ID, VERSION), then I cannot get the identifier for auto-increment.

Is there a workaround, or perhaps a better way to design my table?

---

I was thinking of the following solution:

Table 1:

 create table items { id integer primary autoincrement, version integer} 

table 2:

 create table item_version { id integer, version integer, primary key (id, version) } 

When I add a new element, I add it to the elements and automatically increase the ID. however, if I ever have a new version of the same identifier, I will add it to item_version. I mainly use item_version to store everything except items to create unique identifiers.

Answer 1

Unfortunately, there is no such function; AFAIK exists only in MySQL.

When I need something like this, I just used the following SQL when inserting new rows:

 INSERT INTO items(id,version) SELECT new_id,SELECT COALESCE(MAX(version),0)+1 FROM items WHERE id=new_id) 

This is what worked for me.

You can also create a trigger that will correctly update the version:

 create trigger my_increment after insert on items begin update items set version=(select max(version) from items where id=new.id)+1 where id=new.id and version = 0; end; 

Now, every time you insert a new value into the table:

 > insert into items(id) values(10); > insert into items(id) values(15); > insert into items(id) values(10); > select * from items; 10|1 15|1 10|2 

As you can see, it creates a version for the newly inserted id. Starting with 1. You can tinker with it a bit to get something else.

Answer 2

Are not integer primary keys automatically incremented in SQLlite?