Search code examples
web2py

web2py database id reset after deleting records


Is there a way in Web2py to reset the id of a table after deleting some rows. Let's say I have 15 records and I delete last 5, if I insert a new record it will start with id 16 which I prefer to start with 11.


Solution

  • web2py DAL id fields are autoincrement integer fields, with the autoincrementing behavior handled by the database engine. Unless you have a real need to maintain consecutive id values with no gaps, you probably should not bother with this. (What happens if you need to delete an older record rather than just the most recently inserted record? How will you avoid race conditions?)

    If you must reset the autoincrement sequence, you'll need to consult the relevant documentation for the database you are using. For example, if you are using SQLite, you would update the relevant record of the special sqlite_sequence table as documented here (see example code). When updating the sequence, be sure to prevent any other writes to the table -- otherwise a new record could be inserted with a larger id right before your change.