How to use one sequence for all table in sqlite

Question

When I'm creating tables in an SQLite database, separate sequences are automatically created for each table.

However I want to use one sequence for all tables in my SQLite database and also need to set min and max values (e.g. min=10000 and max=999999) of that sequence (min and max means start value of sequence and maximum value that sequence can increment).

I know this can be done in an Oracle database, but don't know how to do it in SQLite.

Is there any way to do this?

Answer 1

Unfortunately, you cannot do this: SQLite automatically creates sequences for each table in special sqlite_sequence service table.

And even if you somehow forced it to take single sequence as source for all your tables, it would not work the way you expect. For example, in PostgreSQL or Oracle, if you set sequence to value say 1 but table already has filled rows with values 1,2,..., any attempt to insert new rows using that sequence as a source would fail with unique constraint violation.

In SQLite, however, if you manually reset sequence using something like:

UPDATE sqlite_sequence SET seq = 1 WHERE name = 'mytable'

and you already have rows 1,2,3,..., new attempts to insert will NOT fail, but automatically assign this sequence maximum existing value for appropriate auto-increment column, so it can keep going up.

Note that you can use this hack to assign starting value for the sequence, however you cannot make it stop incrementing at some value (unless you watch it using other means).