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

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

  Create table item (id integer auto_increment, version integer default 0, primary key (id, version)); Basically, whenever I include anything in the table, when I start at 0, but whenever VERSION is different, even then multiple items from the same ID With ID permission, the ID needs an ID for auto increment.  
 I am trying to replicate this behavior with SQLite, however, I can not seem to get the table construction task It seems that you only have one column allowed as automurimmant and it should be the primary key. If I make the ID the primary key, then I can not use VERSION as part of the key. However, if I make a multi-column key (id, version), then I can not find the ID for auto increment.  
 Is there any alternative way to design my desk or maybe a better way?  
 Table 1:  
  forming table item {id integer primary autoincrement, version integer}  
 
 table 2: 
 
  table item_version {id integer, version integer, primary key (id, version)}  
 
 If I add items, then I add it to the item and also the ID auto increment. However, if I ever have a new version of the same ID, then I add it to item_version. Basically, I use item_version to capture everything, but to make objects only unique IDs . 
 

Unfortunately there is not

  Enter items (id, Version).   
) SELECT new_id, select COALESCE (MAX (version), 0) WHERE id = new_id from +1 item)  

What does this work from me Does.

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

  Trigger my_increment after inserting the item Update Item = Version Set Do = (Select the maximum (version) of items = new.id) +1 where id = new.id and version = 0; End;  

Now, every time you enter a new value in the table:

  & gt; Enter values ​​(id) values ​​(10); & Gt; Enter item values ​​(15); & Gt; Enter values ​​(id) values ​​(10); & Gt; Choose from item *; 10 | 1 15 | 1 10 | 2  

As you can see that it creates a version for the newly inserted ID starting from 1. You can make a little bit change with this to get something different.