• Welcome, Guest. Please login.
 
August 03, 2020, 10:26:55 am

News:

Welcome to the SQLitening support forums!


insert record into MIDDLE of table using integer primary keys?

Started by bobl, May 06, 2011, 02:33:23 pm

Previous topic - Next topic

bobl

I'm struggling to see how to do this
or whether it's even possible...
therefore...any advice would be much appreciated.

bobl

Is this simply a case of...
ripping through the the records you want to insert BEFORE...
(in REVERSE ORDER)...incrementing their row_ids and then
inserting with a row_id in-between those of the recs either side?

Rolf Brandt

This is not possible. An integer primary key field is always an autoincrement field! See what the SQLite documentation says about it here:

http://www.sqlite.org/faq.html#q1

Quote(1) How do I create an AUTOINCREMENT field.

Short answer: A column declared INTEGER PRIMARY KEY will autoincrement.

Here is the long answer: If you declare a column of a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty. (If the largest possible integer key, 9223372036854775807, then an unused key value is chosen at random.) For example, suppose you have a table like this:

CREATE TABLE t1(
  a INTEGER PRIMARY KEY,
  b INTEGER
);
With this table, the statement

INSERT INTO t1 VALUES(NULL,123);
is logically equivalent to saying:

INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);
There is a function named sqlite3_last_insert_rowid() which will return the integer key for the most recent insert operation.

Note that the integer key is one greater than the largest key that was in the table just prior to the insert. The new key will be unique over all keys currently in the table, but it might overlap with keys that have been previously deleted from the table. To create keys that are unique over the lifetime of the table, add the AUTOINCREMENT keyword to the INTEGER PRIMARY KEY declaration. Then the key chosen will be one more than than the largest key that has ever existed in that table. If the largest possible key has previously existed in that table, then the INSERT will fail with an SQLITE_FULL error code.


New records always will be appended to the end of your database. Where a re record appears in a listing of your records (for example in a listview) depends on the ORDER BY part of your SELECT statement.
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

bobl

Rolf
Thank you very much for your detailed explanation.
As a result of your advice...I won't attempt to manipulate the auto-increment column!

Fred Meier

QuoteIs this simply a case of...
ripping through the the records you want to insert BEFORE...
(in REVERSE ORDER)...incrementing their row_ids and then
inserting with a row_id in-between those of the recs either side?


Yes, that will work but be sure the RowID you are changing is not used as
a foreign key nor is saved as a pointer in other tables.  The follow code
inserts a new RowID 6.  Also could be very costly if you were inserting a
low value RowID in a very large table.  In a large table you may want to
leave gaps between RowID's for faster inserts. 

   slOpen
   slExe "Create Table T1(C1 Integer Primary Key Autoincrement ,C2)"

   for llDo = 1 to 10
      slExe slBuildInsertOrUpdate("T1", "null" & $NUL & "B1")
   next

   slSel "Select C1 from T1 where C1 > 5 order by C1 desc"
   slExe "Begin
   do while slGetRow
      slExe slBuildInsertOrUpdate("T1", "C1 + 1" & $VT, "C1", "C1=" & slFN("C1"))
   loop
   slExe slBuildInsertOrUpdate("T1", "6" & $NUL & "NewB1")
   slExe "End"


Rolf Brandt

Very interesting - I thought for sure that one cannot have any influence on the values of an autoincrement field.

Where would the autoincrementing continue after new records are added? After the highest value?
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

bobl

Rolf/Fred
Eric Pearson on the PB forum suggested using a separate column eg..."sort_order" and manipulating that to overcome such constraints but this stills leaves the question of HOW to do the manipulation which Fred's code answers.
Leaving gaps sounds a very sensible insurance policy against any performance hit too.
Thank you very much indeed to both of you!

Fred Meier

QuoteWhere would the autoincrementing continue after new records are added? After the highest value?

Autoincrement adds a separate table named sqlite_sequence to your
database.  This table contains a row for each of your tables that has the
Autoincrement modifier.  Each row has a column called seq which contains
the highest RowID ever added.  Whenever a new row is added to your table
and the RowID is NULL then RowID will be seq + 1.  If you add a row and
you assign the RowID then seq will be updated if that RowID is higher than
the current value in seq. 

Autoincrement guarantees that a RowID will never be reused.  Example: If
the highest RowIDs were 6892 and 6894 and then you deleted 6894 and then
you added three rows with RowID of NULL.  The new RowIDs would be the
following:
   With Autoincrement = 6895, 6896, 6897
   Without Autoincrement = 6893, 6894, 6895


QuoteEric Pearson on the PB forum suggested using a separate column...

Yes, but only if for some reason you wanted to keep the RowID "pure",
if not then a separate column would be redundant and a waste of resources.