Welcome to the SQLitening support forums!
Started by bobl, May 06, 2011, 02:33:23 pm
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 statementINSERT 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.
QuoteIs this simply a case of...ripping through the the records you want to insert BEFORE...(in REVERSE ORDER)...incrementing their row_ids and theninserting with a row_id in-between those of the recs either side?
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"
QuoteWhere would the autoincrementing continue after new records are added? After the highest value?
QuoteEric Pearson on the PB forum suggested using a separate column...