Database – SQLite: Multi-column primary key with automatic incremental column

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

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

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

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

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;

> 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

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

create table items (
id integer auto_increment,
version integer default 0,
primary key (id, version)
);< /pre> 
Essentially, whenever I insert anything into the table, I want the ID to be automatically incremented, and the VERSION starts from 0, but as long as the VERSION is different, multiple items with the same ID are still allowed.
 
I tried to replicate this behavior with Sqlite. However, I can’t seem to get the table creation to work. It looks like you only allow one column as auto increment, and it must be the primary key. If I use ID as the primary key, then I can’t change the VERSION Used as part of the key. However, if I create a multi-column key (ID, VERSION), then I cannot get the auto-increment ID.
 
Is there a workaround or better way to design me The form? 
 
I am considering the following solutions:
 
Form 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 item, I add it to the item and have ID auto increment. However, if I have the same The new version of the ID, I add it to item_version. Basically, I use item_version to save everything except the item to generate a unique ID.

< p>Unfortunately there is no such function, it only exists in MySQL AFAIK. 
When I needed something similar, I used the following SQL when inserting a new row:

INSERT INTO items(id,version) 
SELECT new_id,SELECT COALESCE(MAX(version),0)+1 FROM items WHERE id=new_id)
 < p>This works for me.
You can also create a trigger that can update the correct 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 a new value is inserted into the table:

> insert into items( id) values(10);
> insert into items(id) values(15);< br />> 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 from 1. You can tinker with a little bit to get something different.