remove gaps in auto increment

Question

Say I have a MySQL table with an auto incrementing id field, then I insert 3 rows. Then, I delete the second row. Now the id's of the table go 1,3. Can I get MySQL to correct that and make it 1,2 without having to write a program to do so?

this is not the intention of a primary key. If you need sequential numbers don't look to the primary key. What are you trying to achieve? — Ian Wood, Mar 17 '12 at 22:17
... (to continue from Ian Wood) if you want sequential numbering and not PK's, use the `rowid`. — slashmais, Mar 17 '12 at 22:19
I'm trying to change the row id's from 1,2,4,5,7,8,etc. to 1,2,3,4,5,6,etc. — John Stimac, Mar 17 '12 at 22:27
Duplicate of http://stackoverflow.com/questions/740358/mysql-reorder-reset-auto-increment-primary-key — Ami, Mar 17 '12 at 22:36
The need to have a "dense" primary key (where there are NEVER any gaps even after deletions) is bad design. If you need such behavior then you need to manage it apart from the primary key. Neither MySQL nor any other database has any builtin functionality to do this as it is unnecessary in the general case. — Jim Garrison, Mar 18 '12 at 01:08

score 6 · Accepted Answer · answered Sep 14 '12 at 16:29

6

MySQL won't let you change the indexing of an Auto-Index column once it's created. What I do is delete the Auto-Index column and then add a new one with the same name, mysql will index the newly generated column with no gaps. Only do this on tables where the Auto-Index is not relevant to the rest of the data but merely used as a reference for updates and deletes.

For example I recently did just that for a table containing proverbs where the Auto-Index column was only used when I updated or deleted a proverb but I needed the Auto-Index to be sequential as the proverbs are pulled out via a random number between 1 and the count of the proverbs, having gaps in the sequence could have led to the random number pointing to a non-existant index.

HTH

answered Sep 14 '12 at 16:29

Mick

76
1
2

but i have some related data in other table with respect to the auto increment id as foreign key,,, then how will i do this ? – ReNiSh AR Jan 11 '14 at 10:00
1

It would probably be better to have a non-PK field be your sequential RNG-friendly index, and just completely ignore the auto-incrementing PK. As others said, auto-increment isn’t meant to be guaranteed sequential, and it isn’t meant for any secondary use. @ReNiShAR , in the unlikely event you still need an answer after six years, see [Barranka's answer](https://stackoverflow.com/a/28507586/1015293). – Frungi Jun 25 '20 at 00:41

score 1 · Answer 2 · answered Feb 13 '15 at 19:47

Quoting from The Access Ten Commandments (and it can be extensible to other RDBMS: "Thou shalt not use Autonumber (or Auto Incremental) if the field is meant to have meaning for thy users".

The only alternative I can think of (using only MySQL) is to:

Create a trigger that adds the row number to a column (not the primary key)
Create a procedure to delete rows and update the row number (I couldn't make this work with triggers, sorry)

Example:

create table tbl_dummy(
    id int unsigned not null auto_increment primary key,
    row_number int unsigned not null default 0,
    some_value varchar(100)
);

delimiter $$

-- This trigger will add the correct row number for each record inserted 
-- to the table, regardless of the value of the primary key    
create trigger add_row_number before insert on tbl_dummy
for each row
begin
    declare n int unsigned default 0;
    set n = (select count(*) from tbl_dummy);
    set NEW.row_number = n+1;
end $$

-- This procedure will update the row numbers for the records stored
-- after the id of the soon-to-be-deleted record, and then deletes it.
create procedure delete_row_from_dummy(row_id int unsigned)
begin
    if (select exists (select * from tbl_dummy where id = row_id)) then
        update tbl_dummy set row_number = row_number - 1 where id > row_id;
        delete from tbl_dummy where id = row_id;
    end if;
end $$

delimiter ;

Notice that you'll be forced to delete the records one by one, and you'll be forced to get the correct primary key value of the record you want to delete.

Hope this helps

remove gaps in auto increment

2 Answers2

Linked