Deleting Last inserted Id in the database

Question

Possible Duplicate:
MySQL - Auto Increment after delete

I want to delete the last inserted ID on my database and restart the numbers.

For example:

ID = 1, 2, 3, 4, 5

If I delete 5, after I add again the ID should still be 5.

I think it's ok to delete as I will just delete to the last inserted Id, so my data will not be unorganized.

My key is AUTO_INCEMENT and NOT NULL.

Additional restriction:

If I have data (1,2,3,4,5), then 5 is the only one who is subject for deletion.

How about deleting not the last one? For example, you delete 3, then new inserted id should be 3? — Thit Lwin Oo, Feb 19 '12 at 01:59
@ThitLwinOo as I said, I will only delete the last inserted. the last primary key will be the one that I will delete — rj tubera, Feb 19 '12 at 02:01
if I have data (1,2,3,4,5). 5 is the only one who is subject for deletion. — rj tubera, Feb 19 '12 at 02:03
How come this question is coming up every few hours? 1) you don't want to do that and 2) if you want to use the sequences generated for some sort of a display - create a trigger. There are **many** implications of reusing deleted numbers. Do yourself a favor and don't do it. — N.B., Feb 19 '12 at 02:16
@N.B. sir, what is the disadvantage of doing this? I really wanna know — rj tubera, Feb 19 '12 at 02:54

score 3 · Accepted Answer · answered Feb 19 '12 at 01:59

3

delete from mytable where id = 5;
alter table mytable auto_increment = 5;

answered Feb 19 '12 at 01:59

Bohemian

1 Answers1