On Friday, 14 October, 2016 07:29, Thom Wharton 
<twhar...@northpointdefense.com> wrote:

> 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
> updated.

> 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 
BY clause.

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.

See https://en.wikipedia.org/wiki/Relational_model

> Thanks,
> Thom Wharton

sqlite-users mailing list

Reply via email to