MySql auto increment

hello experts,

I have a created a table with primary key as auto increment and i am accessing that table from my application using Hibernate.

For example,i have inserted 10 records from 1 to 10 as primary key(auto incremented)....if i delete any record from my application and inserts new record it is entering as 11 th record........how to make the table such that it should not break the sequence.

If i delete 5th record, the next record(6th) should occupy that place.....is it possible..!!

Thanks..!!
perfectobjectAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
K VConnect With a Mentor Database ConsultantCommented:
Delete doesn't cause auto increment value to reset. This happens only when you truncate.
If you want to manually change auto increment value, you may :
ALTER TABLE tblname AUTO_INCREMENT = <NUMBER>;

And this too you may do for last columns, Mysql will not let you "fill the gaps"...
In case if you have records 1..10 and you delete record #5. and do:
ALTER TABLE tblname AUTO_INCREMENT = 5;
You will get error when you'll insert for next record as auto increment will start counting from 5... So 6 will be present in your table..

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
0
 
Om PrakashCommented:
Then instead of making the column as auto incerment, you can manually insert data.

Find the max + 1 value from this table and use this value to insert in new record.
0
 
K VDatabase ConsultantCommented:
@om_prakash_p: This will still not satisfy the need.
You cannot fill the gap :) if deleted 5th, you cannot generate that.
It seems this is not possible.
0
 
nasirbestCommented:
yes, it is not possible, but with complex and useless programming.

however if you so concious about sequence why you delete records from table? only a update can do the job

*. add one more column in db 'enabled'
*. update / set 'enabled' to false/true as per your needs.
*. in where clause of select statements check only 'enabled'
0
 
perfectobjectAuthor Commented:
Thanks..!!
0
All Courses

From novice to tech pro — start learning today.