resetting SQLite auto-increment values

[glenn9]

Dear all,

I'm wanting to reset the auto-increment number in column 'nr' so that it is in sync with the 'rowid' column after a record is deleted.

Below screenshot shows the rowid = 3, but the autoincrement nr = 4 after record 3 was deleted.

I've tried resetting the 'nr' column with this code:

Code: Select all

 openDB
   put "UPDATE sqlite_sequence SET seq = (SELECT MAX(nr) FROM thinkDB)" into tSQL #
   put revdb_querylist(,,gConID,tSQL) into tList
   revExecuteSQL gConID,tSQL
   closeDB

but sadly with no luck!

Grateful for any suggestions or hints!

Thanks,

Glenn

[AxWald]

Hi,

tried manually. Judging by the statement that SQLite Manager created, this should work:

Code: Select all

UPDATE `main`.`sqlite_sequence` SET `seq` = '1' WHERE  `name` = 't_table'

(You forgot a WHERE clause/ row selector ...)

Besides, using autoincrement for anything but invisible id's isn't this good an idea. This always leads to headaches.

Have fun!

[glenn9]

Hi AxWald,

I've probably got the syntax wrong but have put the code into LC as follows:

Code: Select all

   
   openDB
  
   put "UPDATE `main`.`sqlite_sequence` SET `seq` = '1'" & CR & "WHERE  `name` = 'thinkDB'" into tSQL
   
   
   put revdb_querylist(,,gConID,tSQL) into tList
   revExecuteSQL gConID,tSQL
   closeDB

but autoincrement is still out of sync?

(I've also tried a table without an autoincrement column but I still have the same issue)

Not sure what I'm doing wrong!

Thanks

Glenn

[AxWald]

Hi,

a.) "thinkDB" really is the table as it should be?
b.) You missed a space before "WHERE", and no need for a line break here.
c.) "revdb_queryList()" will not run an "UPDATE ..." statement, use "revdb_execute()" instead. Or "revExecuteSQL" as command, as you did in the following line. So skip this line here.
d.) What are the return values of the functions/ cmds? Always check these for errors!
e.) Does it change the value in `sqlite_sequence`.`seq`?

Assuming "thinkDB" actually is the table concerned, try this:

Code: Select all

/* First: Reset sequence:   */
   put "UPDATE `main`.`sqlite_sequence` SET `seq` = '1' WHERE `name` = 'thinkDB'" into tSQL
   get revdb_execute(gConID,tSQL)
      if it is not a number then
      answer "Error! Reason:" & CR & it
      exit whatEver
   end if

/* Second: Check if it worked:   */
   put "SELECT `seq` FROM `main`.`sqlite_sequence` WHERE `name` = 'thinkDB'" into tSQL
   get revdb_queryList(tab,CR,gConID,tSQL)
   if it begins with "revdberr" then
      answer "Error! Reason:" & CR & it
      exit whatEver
   end if
   put it     --  it must be "1"!

Have fun!

[glenn9]

Hi AxWald,

Many thanks for your interest and help

your code works as expected in that I see the change of value in `sqlite_sequence`.`seq`, but the autoincrement of the database stubbornly refuses to update!

I'm guessing its the way that I've set up things that is causing the problem... but I can's see where I'm going wrong!

Not sure if its of help but have uploaded my stack just in case its of help or interest.

Thanks again,

Glenn

[marksmithhfx]

Hi AxWald,

Many thanks for your interest and help

your code works as expected in that I see the change of value in `sqlite_sequence`.`seq`, but the autoincrement of the database stubbornly refuses to update!

Hi Glenn, as I understand it, autoincrement always chooses a number that is one higher than the highest rowid ever used in the database. If you don't use autoincrement, then SQLite assigns a rowid that is one higher than the current max rowid in use. So, for the second condition, for a new row, rowid = max(rowid) + 1. In the first case, SQLite tracks maxrowid over the life of the database, and any new record is assigned maxrowid + 1 where maxrowid is the maximum rowid ever seen. The sole purpose of autoincrement in SQLite is to not reuse previously used record numbers (whether they have been deleted or not).

You can access the rowid of any row directly as "rowid", "_rowid_" or "oid". Also, when you insert a new row, you can get the rowid assigned by SQLite using "SELECT last_insert_rowid()".

I don't know if this helps, as I don't know what you are attempting to do with nr, but maybe it will.

Mark

[AxWald]

Hi,

as Mark said. You cannot coerce SQLite to fill in missing ids "in between" if you use AUTOINCREMENT - independent of the value of "sqlite_sequence.seq", a new id will be always > max(id). So, if you deleted records at the end of the table, just setting the "seq" to 0 or 1 will cause max(id)+1 to be used for the next INSERT. This will also correct the "seq" value.

As mentioned, this isn't for fields with values that are visible. Use it this way:

Code: Select all

`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

as a proper id - visible only in code, used to link records in different tables. This is a very important feature - these ids should never be changed, else all hells will break loose. Been there, survived that. Barely.

If you need a field with numbers nicely ascending, without gaps, you'll have to code it yourself. Depending of your needs there's quite some methods for this.

Have fun!

[marksmithhfx]

I'm wanting to reset the auto-increment number in column 'nr' so that it is in sync with the 'rowid' column after a record is deleted.

Below screenshot shows the rowid = 3, but the autoincrement nr = 4 after record 3 was deleted.

DB.png

Glenn, now I see your problem. You are mistaking the first column you see (the one with values 1-4) as the rowid. It's not. The "nr" column you created is in fact an alias for rowid in an SQLite database. They are the same thing. Try doing this... create a new db with fields: dt, keyword, detail. Add 5 records as before, and delete record 3. Then run this:

Code: Select all

SELECT
	rowid,
	dt,
	keyword,
	detail
FROM
	thinkDB;

and this is what you'll get:

So if that is not the result you are looking for, perhaps if we knew a bit more about what you are attempting to achieve we could make a suggestion.

Best,
Mark

[glenn9]

AxWald, Mark,

Many thanks for your help and suggestions - now I get it!!

I was originally wanting sqlite to restore sequential numbering following a record deletion, and now realise that this isn't possible within SQLite if the deleted record isn't the last one added. It was this that was confusing me and which you have helped me understand - thank you.

Knowing this, I think I can now work out how to get the same effect through importing the SQLite data into LC and then manipulating the data within LC.

Thank you again.

Regards,

Glenn

[marksmithhfx]

Knowing this, I think I can now work out how to get the same effect through importing the SQLite data into LC and then manipulating the data within LC.

Hi Glenn, let us know how you get on. There is always more than 1 way to skin a cat

Cheers,
Mark