1

I'm wondering how does SQLite deal with adding new rows after deleting some rows before it. For example I have 3 records/rows and their 3 auto increment ID's :

ID 1 
ID 2
ID 3

I delete the record with ID 2, so now I have :

ID 1
ID 3

Now I add a new record / row.

Does that database add ID 4 or does it add ID 2 (when its auto increment)? I suppose it just adds +1 from the last record but I am not sure. But if it does, how does that affect the database size? If I often delete / add new records the ID will be a million before you know it? Would that be any kind of problem? Is there anyway for auto increment to fill the skipped ID numbers (which were deleted)?

user1880779
  • 2,038
  • 8
  • 30
  • 40
  • In that case it will add the `ID 4`, and there is no way to set the autoincrement to fill those skipped ID's. – Alexandru Chirila Feb 20 '13 at 20:56
  • @RexLakio Should I consider a big future ID number as a problem (database size/optimization etc)? – user1880779 Feb 20 '13 at 20:57
  • I really don't think it will have a big impact on the efficiency of your program, but you can always run some tests: a couple of thousands deletes and insert, and then check the speed of retrieving from the database. – Alexandru Chirila Feb 20 '13 at 20:59
  • Also, I don't know if any SQL type database will fill skipped ID's with autoincrement; but I can really say for sure. – Alexandru Chirila Feb 20 '13 at 21:02

2 Answers2

0

Because SQL does not care about the current row to be deleted, it will always use the last known incremented value when creating a new row.

Ex:

20 21

10000 10001

This is how standard SQL works with autoincrement.

JoxTraex
  • 13,423
  • 6
  • 32
  • 45
0

Many of your questions are indirectly answered on SQLite's AUTOINCREMENT page, but I'll present a condensed version.

Does that database add ID 4 or does it add ID 2 (when its auto increment)?

It will use 4.

But if it does, how does that affect the database size?

The size of the primary key's index will grow, but the data in each row will increase the database's size much faster than a numeric index. So it isn't a concern for an app.

If I often delete / add new records the ID will be a million before you know it? Would that be any kind of problem?

The largest id SQLite can hold is 9223372036854775807, it's slightly more than a million. Let's assume that you (somehow) increment this id each millisecond and your app runs continuously, you will reach this limit in less than 300,000 years.
Don't lose hope! After this SQLite will choose ids at random searching for one that is unused, only after checking a "reasonable number" of ids will SQLite give up and throw an error claiming it's full.

Is there anyway for auto increment to fill the skipped ID numbers (which were deleted)?

I'm sure you could write a query to do this, but finding these vacant indices would only slow your app down. So considering the math above: I wouldn't sweat it. :)

Sam
  • 86,580
  • 20
  • 181
  • 179