How to solve SQLite auto increment gap?

1

I have a code that deletes a whole row of a database , but there is a problem because I get a hole getting an ID with no data (which I deleted) by this being autoincrement , does not delete the ID value. How do I solve the problem?

    
asked by anonymous 19.07.2017 / 00:08

1 answer

4

There is no problem with this, unless you have set up a system that requires this, which is a bad idea and should review this. IDs should be unique and sequential, but not necessarily continuous.

Even though they are fiscal notes that required continuity, the number should be used as ID and invoices could not be removed, which goes for anything. Everything that must be continuous should not be removed. It is possible to mark as invalid line for use, but leave it there.

If you still want to take advantage of removed IDs, which I do not advise, you can use the above-mentioned marker and throughout the query to filter out what is logically removed and create a table called freelist in> and store there IDs that are removed, there before doing a normal insert it checks if it has an ID there, if you have to use it to update the line, being careful to clean everything, instead of inserting a new line. If you do not have free IDs, it does a normal insertion. If you do not know how to do this right you will either have a race condition or you may have problems with references to the previously used ID. It's so hard to do it right, it's better not to do it.

    
19.07.2017 / 00:56