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.

im confused here , i create a table and have 2 columns id and name.

Now id is primary key , not null and auto increment.

Suppose i fire: insert into table (name) values('test');

this will give me:

1  | test

Now if i fire : alter table table_name auto_increment = 5;

this will populate id with 5 on next insert.

now if i delete value of id =5 and again re-insert : insert into table (name) values ('test2');

it gives me :

 6  | test2

why it doesn't give me 5 again?

Also is it not possible that we can't reset the existing auto_increment values to desired one in MySql?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

I think you will find the answer in MySQL documentation: http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html

Note in particular how the 'innodb_autoinc_lock_mode' option/variable affects this. In MySQL 5.1+ this variable is "1" as default - in 5.0 and before it was "0". The reason to set it to "1" is that it improves performance of replication in some scenarios. If you don't use replication it makes no difference and setting it to "0" should avoid the 'gaps' in the autoincrement counter in most cases.

But there is not - and never was - any guarantee (in MySQL or any RDBMS) that auto_increment values will always be sequential without gaps. The purpose of auto_increment values is - and ONLY is - to ensure a unique identifcation of each row. The value itself has no importance.

share|improve this answer
    
well..this provides one of the basics of SQL –  user21546 Sep 18 '13 at 10:31

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.