CODEWITHSUNDEEP
javaandroidsql
markato
markato

Reputation: 165

How to work with SQLite ID Autoincrementation?

I´m accessing my items in a database via their IDs but when I delete row, and then insert something, the ID is also incremented therefore leaving a gap between IDs.

E.g. Item1(ID=1), Item2(ID=2), Item3(ID=3) ==DELETE+INSERT===> Item1(ID=1), Item2(ID=2), Item3(ID=4)

What is the workaround here, when I want to access the items via their IDs?

Upvotes: 0

Views: 62

Answers (2)

Sanjay Ravichandran
Sanjay Ravichandran

Reputation: 44

The ID does not have to be the same as the item number. Each ID is unique, so whether Item3 (ID==1) or (ID==3) doesn't matter. The ID will still be unique.

Upvotes: 0

zedfoxus
zedfoxus

Reputation: 37029

Covering 3 things here:

  1. Manage incrementing yourself
  2. Change auto-increment sequence
  3. Don't worry about gaps

Manage incrementing yourself (without autoincrement)

You don't have to use autoincrement ID like Nicolas mentions in his comment. You can enter that yourself manually or inline query like this:

create table test (id, name);
insert into test select coalesce(max(id)+1, 1), 'john' from test;
select * from test;
id          name      
----------  ----------
1           john      

insert into test select coalesce(max(id)+1, 1), 'mary' from test;
select * from test;
id          name      
----------  ----------
1           john      
2           mary      

insert into test select coalesce(max(id)+1, 1), 'abcdefg' from test;
sqlite> select * from test;
id          name      
----------  ----------
1           john      
2           mary      
3           abcdefg

delete from test where id = 3;
insert into test select coalesce(max(id)+1, 1), 'rubio' from test;
sqlite> select * from test;
id          name      
----------  ----------
1           john      
2           mary      
3           rubio

Use autoincrement but reset the next value

You can use an integer autoincrement field and reset the next sequence to the next value so as to not leave a gap like this:

create table test1 (id integer primary key autoincrement, name text);
insert into test1 (name) values ('john');
insert into test1 (name) values ('mary');

-- let's create a gap
delete from test1 where id = 2;

-- let's fill the gap back up
update sqlite_sequence set seq = (select max(id) FROM test1) where name='test1';
insert into test1 (name) values ('tony');
select * from test1;
id          name      
----------  ----------
1           john      
2           tony

See documentation about this here: https://sqlite.org/fileformat2.html#seqtab

Don't care about perfect IDs

While you want your IDs to be sequential, I'd say - don't worry about gaps. You are giving an ID to each row to uniquely identify them. If first row is uniquely identified by 100 and second row is uniquely identified by -90, so what? Let it be that way.

My recommendation is to keep autoincrement the way it is. If you delete a row in the middle of the table, that's fine. Don't worry about gaps.

Upvotes: 1

Related Questions