CODEWITHSUNDEEP
iphoneiosipadsqlite
Ashwani
Ashwani

Reputation: 388

Is there any way to insert data in sqllite table sequentially?

I want to insert some data in SQLite table with one column for keeping string values and other column for keeping sequence number.

SQLite documentation says that autoincrement does not guarantees the sequential insertion. And i do not want to keep track of previously inserted sequence number.

Is there any way for storing data sequentially, without keeping track of previously inserted row?

Upvotes: 2

Views: 530

Answers (3)

mvp
mvp

Reputation: 116317

Short answer: Stop worrying about gaps in AUTOINCREMENT id sequences. They are inevitable when dealing with transactional databases.

Long answer: SQLite cannot guarantee that AUTOINCREMENT will always increase by one, and reason for this is transactions.

Say, you have 2 database connections that started 2 parallel transactions almost at the same time. First one acquired some AUTOINCREMENT id and it becomes previously used value +1. One tick later, second transaction acquired next id, which is now +2. Now imagine that first transaction rolls back for some reason (encounters some error, code decided to abort it, program crashed, etc.). After that, second transaction will commit id +2, creating a gap in id numbering.

Now, what if number of such parallel transactions is higher than 2? You cannot predict, and you also cannot tell currently running transactions to reuse ids that were not used for any reason.

Upvotes: 1

Ravi Raman
Ravi Raman

Reputation: 1070

If you insert data sequentially into your SQLite database, they will be stored sequentially.

From the Documentation: the automatically generated ROWIDs are guaranteed to be monotonically increasing.

So, for example, if you wanted to have a table for Person, then you could use the following command to create table with autoincrement.

CREATE table PERSON (personID integer PRIMARY KEY AUTOINCREMENT, personName string)

Link: http://www.sqlite.org/autoinc.html

Upvotes: 0

Rob
Rob

Reputation: 437872

The short answer is that you're right that the autoincrement documentation makes it clear that INTEGER PRIMARY KEY AUTOINCREMENT will be constantly increasing, though as you point out you using, not necessarily sequentially so. So you obviously have to either modify your code so it's not contingent on sequential values (which is probably the right course of action), or you have to maintain your own sequential identifier yourself. I'm sure that's not the answer you're looking for, but I think it's the practical reality of the situation.

Upvotes: 1

Related Questions