Make sure SQLAlchemy doesn't re-use numeric ID's

Question

I am using SQLAlchemy (currently testing on SQLite, but need it to work elsewhere too) and I have a problem with primary key ID's being reused.

For instance, If I have some content with ID's from 1-6, then delete the content with an ID of 4, I would still like the next content added to have an ID of 7 and not 4 (as it currently happens). The ID's can be reused after a Session reset (or not, doesn't really matter either way).

I'm guessing that this depends on the type of DB you are using (and it's auto increment policy/implementation), but I would need a way to get this to work in at least SQLite, mySQL and PostgreSQL (others aren't important but they wouldn't hurt either).

Any help or pointing in the right direction would be appreciated!

Answer 1

This is a RDBMS-specific feature, not something SQLAlchemy needs to concern itself with.

Usually databases will use monotonically incrementing ids (new values being higher than preceding values, usually by +1 but larger steps are possible).

The only real exception here is SQLite; it will give you the highest currently used value plus one. That means that if you delete MAX(id) from a table, then that id is likely to be reused for a future row insertion. See the SQLite autoincrement documentation. Note that the behaviour you claim in your post does not actually ever happen; either MAX(rowid) + 1 is picked, or SELECT seq + 1 FROM sqlite_sequence WHERE name=<tablename>; is used instead.

You can switch to proper auto-incrementation (where values are never reused) by setting sqlite_autoincrement=True for a table. See the specific dialect documentation.

If you are planning to use Oracle as a backend, you also need to explicitly add a Sequence() to a column that needs to auto-increment.