sqlite增量键超过2个表

Question

Im working on a pos system based on sqlite. One table contains all orders/buys. Every night the system should copy all orders to an different table in a different database, because otherwise the orders table will become too heavy (monthly about 15000 new rows) and the whole system will become too slow.

My problem is, that when i copy the daily orders into the backup database, im loosing my last autoincrement value. That means: sqlite is starting again with 1. But i need a unique ID for the orders over 2 databases/tables?

Does somebody has an idea, how i can force sqlite to not reset the last autoincrement value, when deleting all rows?

Answer 1

CREATE TABLE backup_table(
  tableflag integer not null,   
  autoincid integer not null,  
  column-n   ....,
  ....., 
  PRIMARY KEY (tableflag , autoincid)
);

and copy the original autoincrement value from the original table to your backup table and use a special flag for every table you want to unite

Otherwise you could insert a row with the highest autoincrement value plus 1 of your backup table after the deletion as first row, Then sqlite will just increment that value.