Hi Igor, Keith,
I think my explanation wasn't very clear.
I just meant to say that ROWID is not a sequence number of insertion in
the case when an INTEGER PRIMARY KEY is used - it comes across as a
sequence number when we don't have an integer primary key.
Rest of the answers less relevant now - but inline.
On 11/2/2013 11:45 AM, Igor Tandetnik wrote:
On 2/10/2013 10:06 PM, Mohit Sindhwani wrote:
The OP reads records in a loop. I imagine he or she may simply use a
loop counter as a sequence number, if that's what is needed. However,
I too read the original question as asking for a unique identifier,
such as ROWID, rather than a sequence number (which, as I said, is
easy to obtain). The OP is asking for "record number that sqlite
creates when it creates your record" - that is, the ID generated on
row insertion, also known as ROWID.
That is, of course, correct.
2. The problem with INTEGER PRIMARY KEY.. it is not representative of
what we expect from a ROWID. We expect ROWID to be a sequential number
that increases with every record insertion.
Who do you mean "we", Kemo Sabe?
By "we", I probably meant myself :) When I saw ROWID first, I assumed
it was a sequence number of sorts.. and that lead to all sorts of
problems. My intention was to warn someone else to not make that
assumption.
However, if you have
something like num INTEGER PRIMARY KEY, you will be able to do this:
insert with num=1 #rowid = 1
insert with num=2 #rowid = 2
insert with num=4 #rowid = 4
insert with num=3 #rowid = 3
It could be argued that the OP wanted the 3rd record to have a row
number of 3, rather than 4.
Why then would the OP insert the row with num=4, rather than 3? Why do
the extra work for the sole purpose of hurting your goal? That doesn't
make much sense.
Was meant to be an illustration that ROWID is not a sequence number...
So, it doesn't give the concept of sequence at all since an INTEGER
PRIMARY KEY needs to be unique, but does not have to be monotonically
increasing.
Well, if you go out of your way to intentionally break the
monotonically increasing sequence, then you end up with a broken
sequence. Gun, meet foot.
Again, meant to illustrate that when you are not using an INTEGER
PRIMARY KEY, the ROWID is monotonically increasing. When using an
INTEGER PRIMARY KEY, that will not be so. So, just be careful if you
intend to do something like select * from mytable ORDER by ROWID DESC
LIMIT 1; and hope to select the last inserted record if you have an
INTEGER PRIMARY KEY on that table.
I have been caught out by this - I read what the documentation says but
just did not carefully understand it. What the above means is this:
* You do an insert in the sequence as above, you say that I should not
sort by id ASC because you want it in insertion order
* You decide then to do a sort by ROWID ASC - expecting that ROWID is
maintaining the sequence since when you do a general SELECT * from
mytable; you often get it in insertion sequence (if I'm not wrong).
You are wrong. Normally, with a simple SELECT like this, you get the
results ordered by ROWID. SQLite table is stored as a b-tree, with
ROWID as its key, so that's the natural order of traversal.
Learn something every day :)
Thanks!
Best Regards,
Mohit.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
