sqlite: multi-column primary key with an auto increment column

Question

I basically want to convert a table from mysql to sqlite with the following scheme:

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

Essentially, I want ID to auto increment whenever I insert anything into the table, with VERSION starting off at 0, but still allow multiple items with the same ID as long as VERSION is different.

I'm trying to replicate this behavior with Sqlite however, I can't seem to get table creation working. It seems like you are only allowed one column as autoincrement and it has to be the primary key. If I make ID the primary key, then I can't use VERSION as part of the key. However, if I make a multi-column key (ID, VERSION) , then I can't get ID to auto increment.

Is there a workaround or perhaps a better way of designing my table?

Answer 1

Don't Integer Primary Keys automatically increment in SQLlite?

Answer 2

Unfortunately there is no such feature, it exists AFAIK only in MySQL.

When I need something like that I just used 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 what worked from me.

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

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, each time you insert a new value into 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 version for newly inserted id. Starting from 1. You can tinker with it a little in order to get something different.

Answer 3

I was thinking of the following solution:

table 1:

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

table 2:

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

When i add a new item, i add it to items and have ID auto increment. however, if i ever have a new version of the same id, i add it to item_version. basically, i use item_version to hold everything but items to just to generate unique ids.