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.
Thanks
Delvin
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Jens Alfke
Sent: Saturday, 15 October, 2016 13:42
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
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 <kmedc...@dessus.com> 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.
—Jens
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
Richard Damon
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
