CODEWITHSUNDEEP
sqliterowid
Nadjib Bendaoud
Nadjib Bendaoud

Reputation: 524

Reset rowid field after deleting rows

I'm using an sqlite database, and i want to reset rowid field after deleting rows. my table doesn't have a primary key or a specified Id, i'm only using the default rowid given by sqlite.

  NOM        TYPE     CL     VAL   SP        TP

 "test1"    "test1"  "1"    "1"  "test1"    "test1"
 "test2"    "test2"  "2"    "2"  "test2"    "test2"
 "test3"    "test3"  "3"    "3"  "test3"    "test3"

When i delete one or multiple rows, i want the default rowid to reset automatically, so i have read some answers and found that we can do that by using sql_sequence table .

delete from sqlite_sequence where name='table_name'

the problem is that i can't find that table in my sqlite 

[ no such table: sqlite_sequence ]

Is there any solution to my problem

Upvotes: 1

Views: 6035

Answers (3)

Nadjib Bendaoud
Nadjib Bendaoud

Reputation: 524

Thanks for all your answers i found what i was looking for and it quite simple 

DELETE FROM 'table_name' WHERE col='value';
REINDEX 'table_name';

and this will rebuild the specified table index, so if all the rows are deleted the index is reset to 0, and if is still row in the table, the index will be reordred correctly.

Upvotes: 4

MikeT
MikeT

Reputation: 56943

A table by default will have a rowid that does not use sqlite_sequence table.

It is only when an alias for the rowid is specified (defined using column_name INTEGER PRIMARY KEY or column_name INTEGER PRIMARY KEY AUTOINCREMENT) AND that the definition of the alias includes the optional AUTOINCREMENT keyword/phrase, that the algorithm for determining the rowid utilises the sqlite_sequence table to ensure that the rowid is greater than the last used rowid (typically it will be greater anyway).

Even if you used AUTOINCREMENT and thus the sqlite_sequence table existed and you deleted the row for the table from the sqlite_sequence table. This would not reset the sequence as I believe the algorithm used uses the higher of the highest existing rowid in the table and the value obtained from the sqlite_sequence table. here's a post I made in realtion to this

There is likely very little, or no, reason to utilise the rowid as if it's something other than the means to uniquely identify a row.

However, if you really want to manage the rowid then you can specifically set this value by specifying the rowid column (noting that there is an implied UNIQUE constraint).

So you could for example use :-

INSERT INTO yourtable (rowid, NOM, TYPE, CL, VAL, SP, TP) VALUES(123,'MyName','Testx', '123', '123', 'Testx', 'Testx');
  • rowid will be set to 123

You could also use update a rowid e.g. :-

UPDATE yourtable SET rowid = 5 WHERE rowid = 123;

- rowid that was 123 is changed to 5 unique constraint permitting

You could obtain the highest rowid using:-

SELECT max(rowid) from yourtable;

You could even use :-

INSERT INTO yourtable (rowid, NOM, TYPE, CL, VAL, SP, TP) VALUES((SELECT max(rowid) from yourtable)+1,'MyName','maxx', '???', '???', 'Maxx', 'Maxx');
  • insert with the next rowid sequence.

Note the above examples are without an alias of the rowid, obviously they not rely upon the non-existant sqlite_sequence table.

Of course you could always have your own equivalent of the sqlite_sequence table, just that it CANNOT be named this nor could it's name start with sqlite. Note! this would be the only persistent way using SQLite of resetting the id, but could be a nightmare

All the above assumes that :-

and i want to reset rowid field after deleting rows

is not ....after deleting ALL rows, as if this were the case the rowid would be effectively reset (basically without AUTOINCREMENT rowid is determined according to the highes existing rowid, so if none exist then the rowid will be 1).

  • See example 2 below.

NOTE!!

This answer is not condoning or recommending any of the above.

Example 1

using the following :-

CREATE TABLE IF NOT EXISTS rowidtable (NOM TEXT, TYPE TEXT, CL TEXT, VAL TEXT, SP TEXT, TP TEXT);
INSERT INTO rowidtable (rowid) VALUES(10);
INSERT INTO rowidtable (rowid) VALUES(12);
INSERT INTO rowidtable (rowid, NOM, TYPE, CL, VAL, SP, TP) VALUES(123,'MyName','Testx', '123', '123', 'Testx', 'Testx');
INSERT INTO rowidtable (NOM, TYPE, CL, VAL, SP, TP) VALUES('MyName','Testx', '123', '123', 'Testx', 'Testx');
UPDATE rowidtable SET rowid = 5 WHERE rowid = 123;
SELECT max(rowid) from rowidtable;
--// Following repteated 3 times (so rowid's 125,126 and 127 used respectively)
INSERT INTO rowidtable (rowid, NOM, TYPE, CL, VAL, SP, TP) VALUES((SELECT max(rowid) from rowidtable)+1,'MyName','maxx', '???', '???', 'Maxx', 'Maxx');
select rowid,* from rowidtable;

The resultant table is :-

enter image description here

  • Rows with id's 10 and 12 were inserted first (nulls (red) for other values)
  • Row with id 5 was originally row with id 123
  • Rows with id's 125, 126 and 127 were inserted using the rowid as obtained via subquery that adds 1 to the maximum current rowid (i.e. last insert run 3 times).

Example 2

If the following were then used :-

DELETE from rowidtable
INSERT INTO rowidtable (NOM) VALUES('after mass delete');

Then the resultant table would be :-

enter image description here

Upvotes: 3

Mark Benningfield
Mark Benningfield

Reputation: 2892

The sqlite_sequence table is only used for tables that have a INTEGER PRIMARY KEY AUTOINCREMENT column defined.

If you are relying on the value of the internal defaultrowid for a given row in a table, then you should define a named autoincrement primary key for that table, then you can reset the counter using the sqlite_sequence table.

Upvotes: 1

Related Questions