Summary of what I have seen:

Schema for table has an auto-increment primary key, and a Date-Time field (which has duplicate values)

Records are to be retrieved in Date-Time order, with duplicate values needing to be retrieved in a consistent (and perhaps even specified by something else)

The OP was careful to initially insert the records in Date-Time order, and records with identical Date-Times in the 'proper' order (so the auto-increment field just happens to be the proper total order).

He then wants to later add a new record, and wants the auto-increment to 'automatically' update to keep it in total order.

It has been pointed out that this is NOT the way an auto-increment field is intended to work.

Some people have come out with various ways to implement a sort field that leaves gaps to allow at least some level of inserting new records between old records.

My thought is that if the first level of sort IS by Date-Time, and then by some other condition, then the real solution is to use an ORDER BY clause on the Date-Time field and then some other field to implement the duplicate value ordering (and likely have a covering index so this ORDER BY is efficient). This extra field doesn't need to be globally unique, just unique within a given value of Date-Time, so the practical issues in processing the primary key are greatly reduced (at minimum, if you need to renumber records, there are a lot less that need to be renumbered).

On 10/15/16 3:12 PM, Delvin wrote:
Afternoon all,

I am replying to this thread because I am a little confused here.  From what I 
have gotten from this thread, someone wants to be able to insert a record into 
a table based on an arbitrary record number (i.e. if a table already has 
records number 1, 2, 3, 4, etc., have the ability to insert a new record number 
3 and changing the record numbers (old record number) 3, 4, etc.

My confusing is this - are record numbers arbitrary to start with (since the 
table can be sorted in any manner) and the only reason for the record number 
column is to insure that the primary key is unique.


-----Original Message-----
From: sqlite-users [] On 
Behalf Of Jens Alfke
Sent: Saturday, 15 October, 2016 13:42
To: SQLite mailing list <>
Subject: Re: [sqlite] Inserting a new record (anywhere) into a table of ordered 
records that have an integer auto-increment primary key

On Oct 15, 2016, at 11:12 AM, Keith Medcalf <> wrote:

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.
There’s no need to dive into database theory! I’ll play devil’s advocate and 
say that this could pretty easily be done in SQLite by writing a simple 
extension function like in inbetween(a, b) that takes two strings and returns a 
string that sorts in between them, as I described previously.

Then you just make the table’s primary key a string and do
        INSERT INTO mytable (ID, …) VALUES (inbetween($firstid, $secondid), …) 
where $firstid and $secondid are the the primary keys of the two rows you want 
to insert between.

In real life you’d probably just implement inbetween() as part of your program 
instead of as a SQLite function; I just wanted to prove that a relational 
database can in fact do this.


sqlite-users mailing list

sqlite-users mailing list

Richard Damon

sqlite-users mailing list

Reply via email to