CODEWITHSUNDEEP
databasesqliteandroid-sqlitesqliteopenhelper
PRANAV SINGH
PRANAV SINGH

Reputation: 1190

Reset autoincrement values in SQLite database

If my SQLite database has 5 rows and 1st column as autoincremented field and then I deleted the 3rd row then how can I update the value of primary key of all rows in a autoincremented form starting from one.

Initial table:

enter image description here

Table after deletion of row

enter image description here

but I want something like this:-

enter image description here

How can I achieve this result using SQLite queries.

Upvotes: 0

Views: 1611

Answers (1)

MikeT
MikeT

Reputation: 57013

Although the manipulation of Primary Keys is not recommended what you want to achieve can be achieved.

Assuming that by saying "1st column as autoincremented field" that the pk column is defined with the AUTOINCREMENT keyword rather than just an alias of the rowid (which autoincrements anyway see SQLite - AUTOINCREMENT).

Then you could using SQL such as the following (again not recommended):-

/* Create and populate the test table */
DROP TABLE IF EXISTS mytable;
CREATE TABLE IF NOT EXISTS mytable (pk INTEGER PRIMARY KEY AUTOINCREMENT, set_name_fk TEXT, question TEXT);
INSERT INTO mytable VALUES(1,'A','Question0'),(2,'B','Question1'),(3,'C','Question2'),(4,'D','Question3'),(5,'E','Question4');

/* Do the deletion */
DELETE FROM mytable WHERE question = 'Question2';

/* Start the manipulation */
/* Just in case it exists DROP the temporary table */
DROP TABLE IF EXISTS new_othermytable;
/* Create the temporary table to maintain the column definitions */
CREATE TABLE IF NOT EXISTS new_othermytable (pk INTEGER PRIMARY KEY AUTOINCREMENT, set_name_fk TEXT, question TEXT);
/* Populate the temporary table manipulating the pk value accordingly */
INSERT INTO new_othermytable SELECT coalesce((SELECT count(pk) FROM mytable WHERE pk < x.pk),0) + 1 AS pk, set_name_fk, question FROM mytable  AS x ORDER BY pk;
/* DROP the original table */
DROP TABLE IF EXISTS mytable;
/* Rename the temporary table to be the original table */
ALTER TABLE new_othermytable RENAME TO mytable;
SELECT * FROM mytable;

The crux of the above is the query that selects the count of the rows with a lower pk value than the current row (coalesce sets this to 0 if there are no rows) and then adds 1, thus effectively renumbering the pk i.e. SELECT coalesce((SELECT count(pk) FROM mytable WHERE pk < x.pk),0) + 1

The query could be used to get the desired output without having to change the table.

Upvotes: 1

Related Questions