How do I alter the primary keys auto-increment so it dosen't skip values in case I delete a row

Question

I'm doing this homework for school and I would like my primary key (id) to go 1, 2, 3 and so forth. but if I delete a row and add a new person, it skips that number.

This is my query from mySQL workbench

CREATE TABLE IF NOT EXISTS person
(
id              int(11)         NOT NULL auto_increment primary key,
first_name      varchar(45),
last_name       varchar(45)
);

I'm not really sure where I have to look and what to change.

My addPerson statement is this:

public Person addPerson(Person p){

String sql = "INSERT INTO person (first_name, last_name) VALUES(?,?) ";

template.update(sql, p.getFirst_name(), p.getLast_name());

return null;
}

Also im not asking why it shouldn't be done but rather if it's possible or not

You can't and you shouldn't. The whole point of an ID is to be unique. — tkausl, Apr 26 '19 at 15:11
There is no guarantee that an auto_increment will produce contiguous id's even without a delete (or insert in duplicate key) — P.Salmon, Apr 26 '19 at 15:12
What i'm looking for is if i have id: 1,2,3 and I delete 2. It then goes 1,3. I would like to be able to it so when i add a new person it chooses the freed up key — FakeRune, Apr 26 '19 at 15:15
someones identity number will not be given to another person when he die — Syed Mehtab Hassan, Apr 26 '19 at 15:18

score 0 · Answer 1 · answered Apr 26 '19 at 15:15

The whole point of auto increment is so that you don't have to count for yourself. When you query your database you should use another column to do that(like name,age or something else) then from the result you can get the id of that row. So there's really no point in keeping track of the id

Jordan · Answer 2 · 2019-04-26T15:23:29.120

That's not something that you can do. Nor is it something that you'd really ever want to do in a real-world scenario. If keys can be reused, it would cause a significant performance hit of having to do a full table scan every time you add a new record to find an available key (the functionality you're seeing results from simply tracking the highest key value generated). Not to mention the risk of ending up with inconsistent data in instances where you have foreign key constraints disabled for a more complex database.

As such, this isn't an ability that is built into any databases that I'm aware of. Is there a reason (other than aesthetics) for wanting to do this?

How do I alter the primary keys auto-increment so it dosen't skip values in case I delete a row

2 Answers2