Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table which contains an auto incremented primary key id. If I delete the last row (highest id, for exampe id = 6) and insert a new row, the new id starts at 7. Which paramater I have to change that the primary key starts at 6?

CREATE TABLE animals (
 id MEDIUMINT NOT NULL AUTO_INCREMENT,
 name CHAR(30) NOT NULL,
 PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');

Result:
id name
1 dog
2 cat
3 penguin
4 lax
5 whale
6 ostrich

DELETE FROM animals WHERE id = 6;
INSERT INTO animals (name) VALUES
('x');

Result:
id name
1 dog
2 cat
3 penguin
4 lax
5 whale
7 x

Thanks for advice.

share|improve this question
    
So what if there are gaps? (which can appear due to several other reasons, not only by deleting rows) –  ypercube Oct 15 '13 at 17:13
    
what would you like to do if delete happen after some insert? are you going to reuse those gap? is yes then I don't think Identity is right type for that column. it give you an opportunity to not worry about unique values in that column. if you intend to keep values in sequence then you could have just use regular INT/BIGINT type data type. finding/reusing/resetting values for every missing gap is like forfeiting the purpose of Identity Property. –  Anup Shah Oct 15 '13 at 17:19
    
@giordano Why do you want to be an AUTO_INCREMENT PRIMARY KEY neat freak? deal with it there is also an "gap" in your table data if you update/delete due to fragmentation.. –  Raymond Nijland Oct 15 '13 at 18:01
    
I did an investigation about continuous auto_increment values for SQL Server a while back: sqlity.net/en/792/the-gap-in-the-identity-value-sequence - While it is not for MySQL, the underlying mechanisms are the same, so you will get similar results. In short, you can't prevent gaps an auto generated sequences. –  Sebastian Meine Oct 15 '13 at 20:55
    
@ypercube: thanks for answers. Indeed, the gap I got from a another more complex example but I wanted to show a minimal example. The real example was (1) changing the expiry date of a row (e.g. from a product table) due to change of an attribute and (2) add the new product with the same product code, new attrribute, new effective date and open expiry date. What happend is that the new primary key didn't auto incremented with +1 but +2. Obviously, in step (1) or (2) there is an invisible auto increment. I wonder what are the other reason for gaps. –  giordano Oct 16 '13 at 7:51
show 3 more comments

2 Answers

up vote 6 down vote accepted

This is by design - all DBMS act this was with auto-increment columns.

If they did not external referential integrity could be damaged. For a simple example of this, imagine you are storing URLs for a shortening service using an auto-increment column as the key. You don't know if the shortened URL has been given out to anyone yet, and the database certainly doesn't, so reusing ID 1234 could result in someone's poor granny visiting somenastypornsite.xxx instead of loverlyknitting.org when she clicks http://shortthi.ng/1234 in an old email, instead of getting a "sorry but this link no longer exists in our records" message.

Also if you reset the increment after deleting the last item, will you also be going through all the work (or expecting the database to) of renumbering everything after the 5th item of 5 million when the 5th item is removed? complete with changes to other tables where their are foreign key constraints pointing at the increment column? Such extra work could get very expensive IO-wise.

If you do reset the increment point after deleting the last item, do be very very careful of your transaction isolation levels: You might reset it just as another transaction makes use of the value, leading to errors (or worse, silent failures), unless you make sure that your action is fully 100% isolated.

I generally recommend people working with databases read "SQL Antipatterns" which has a chapter on this issue named "Psuedo-Key Neat Freaks" (which covers the matter in a friendlier manner than the chapter title may imply to some!). Essentially, if the value has meaning beyond being a key (or at most carrying insert order information) then it probably should not be an auto-incrementing column, and if it does have no meaning beyond being a key (or at most carrying insert order information) then gaps should not matter.

share|improve this answer
    
Thanks for this explanation. I see that I have to dowse deeper into the key concept. I wonder how the auto increment mechanism works, since I observed also gaps in the primary key when I didn't delete it but changed (updated) one row and added a new one. Definitivly, what I won't do is change the auto increment value manaully. –  giordano Oct 16 '13 at 7:59
    
Regarding "dowse deeper into the key concept", I strongly recommend the SQL Antipatterns book by Bill Karwin - it cover this and a number of other common (and not so common) topics, talking about how to get the wrong, why the common wrong methods can cause problems, how to fix them, and when the "wrong" method might be OK to use (or the only choice) anyway, in a concise but accessible manner. –  David Spillett Oct 16 '13 at 17:18
    
Spillet: Thanks for this indication. I read now and it was very helpful. Nevertheless, I don't understand why InnoDB and MyISAM count differently: dba.stackexchange.com/questions/51883/… –  giordano Oct 28 '13 at 17:39
add comment

You can reset the auto_increment value using the following:

ALTER TABLE MyTable AUTO_INCREMENT = 1234;

You'd need to determine the maximum value present in the table, and add 1 to that value to use in the statement above.

share|improve this answer
    
Bad idea this will lock your table from reading or writing in "almost all" MySQL versions. MySQL 5.6 could do an LIVE ALTER but sometimes it also needs to lock the table. –  Raymond Nijland Oct 15 '13 at 18:04
2  
@RaymondNijland I don't think the lock would last long enough for anyone to notice. –  ypercube Oct 15 '13 at 18:59
    
@Max, Raymond, ypercube: Thanks for answer. I will avoid to do this kind of change. –  giordano Oct 16 '13 at 8:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.