On Friday, 14 October, 2016 07:29, Thom Wharton
> I have a table of records in a Sqlite DB. It contains 5 records. Each
> record has a time-stamp which is not guaranteed to be unique. To
> preserve order (which is important in my project), I've given the table
> an integer primary key (called ID) that is auto-increment. Let's say I
> have the following table...
> ID Date Type Size Data
> 1 10OCT-08:13:47 Ether 28 sddsgsd...
> 2 10OCT-08:13:52 Ether 77 fdasfdsdsddssdg...
> 3 10OCT-08:13:52 Ether 44 zeasfkkfa...
> 4 10OCT-08:13:57 Ether 33 dartdg...
> 5 10OCT-08:14:03 Ether 51 afafsfafa...
> I want to be able to programmatically insert a new record anywhere in
> that table. Let's suppose I want to create a new record between the
> records whose ID are 2 and 3. This new record would need to take the ID
> of 3, and all subsequent records would need to have their primary keys
> Is there a way to do this automagically (like a specialized INSERT
> command?) in Sqlite?
Unfortunately no, there is no way to do this on *ANY* database that uses the
relational database model. You see, in the relational database model there is
no such things as "records". What you have above (a table) consists of a "bag
of tuples". There is no order to them. They are merely a "bag" (known
mathematically as a set). When you issue a query (SELECT) you project sets and
conditions and obtain a set of data as a result (another bag of data).
If you wish an order, you tell it what order to put it in (using an ORDER BY
clause). Due to the inherently sequential nature of modern computing
technology most programming languages are unable to deal with the data in its
native form (a set) and therefore you retrieve and work on them one after each,
in forward order only, in the order you requested presentation using the ORDER
The "integer primary key" (whether you use autoincrement of not) is simply a
unique key in each record that is derived sequentially (unless you provide it)
based on the previous (or biggest ever previously used, if you use
autoincrement) number in that field, to uniquely identify the contents of that
tuple. It does not contain any useful information which you should be
manipulating in any way other than using it as a linkage to perhaps other
related tuples in other tables.
> Thom Wharton
sqlite-users mailing list