CODEWITHSUNDEEP
mysql
aherlambang
aherlambang

Reputation: 14418

autoincrement in MySQL

I have one of my primary key column in my table to auto-increment. However when I delete a row from the table that has the highest primary key id (lets say 11). Then the next time I do an insertion it inserts the key as 12 not 11 (though logically it can use 11 as there is no entry associated with the key 11). How can I make this happen?

Upvotes: 2

Views: 697

Answers (2)

Nanne
Nanne

Reputation: 64399

Are you really sure you want this? An autoincrement column will guarantee a unique number, and that's enough. You could update the next autoincrement value I guess (i'll have to look it up how that works), but I don't think you should want that.

If you need to control the numbers in a column, you should do so manually.

nevertheless, you can change the autoincrement number like so:

ALTER TABLE tbl AUTO_INCREMENT = 100;

(from: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html )

Another remark: If you have numbers one to ten, and you remove 5, you cannot easily do this. You can hardly make the next auto_increment 5 because 6 is already there. So again, while you can do something dirty for your example, it's really hard to do this in a real environment. Maybe start a new question with description of your situation, and ask for advice how to approach that problem without the auto_increment tricks :)

Upvotes: 6

Augusto
Augusto

Reputation: 29907

Mysql doesn't have that feature out of the box, you'll need to code it in your application. One problem you'll have is that if 2 transactions want to get and id, one of the them will get a duplicate id error. Of couse, this is better to avoid.

All the DB engines lack this "feature", as it not good for concurrency.

Upvotes: 0

Related Questions