I essentially wish to convert a table from mysql to sqlite using the following plan:

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

Basically, I would like ID to auto increment whenever I place anything in to the table, with VERSION beginning off at , but nonetheless allow multiple products with similar ID as lengthy as VERSION differs.

I am attempting to replicate this behavior with Sqlite however, I can not appear to obtain table creation working. It appears as if you are just permitted one column as autoincrement also it needs to function as the primary key. Basically make ID the main key, i quickly can't use VERSION included in the key. However, basically create a multi-column key (ID, VERSION) , i quickly can't get ID to auto increment.

It is possible to workaround or possibly an easy method of creating my table?

Regrettably there's no such feature, it is available AFAIK only in MySQL.

After I need something of that nature I simply used following SQL when placing new rows:

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

This what labored from me.

You may also produce a trigger that will update the version properly:

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 place a brand 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 tell it produces version for recently placed id. Beginning from 1. You are able to mess by using it just a little to be able to go different.

Don't Integer Primary Secrets instantly increment in SQLlite?

I believed from 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)
}

After i give a new item, i combine it with products and also have ID auto increment. however, basically have a brand new version of the identical id, i combine it with item_version. essentially, i personally use item_version to carry everything but products to simply to create unique ids.