How to make primary key serially?

suppose i have 20 rows in my table , now i deleted last 4 rows . At the very next time I inserted 2 more rows then the primary key no. will be

1,2,3...16,21,22.

Is there any way to restore that key , means any way to store the row at very next of 16 and the res开发者_JAVA技巧ult will be

1,2,3...16,17,18 

---

You don't need this. Just believe me!

---

It's a bit of a nasty thing to do really like Vadim says. Also a dangerous game to play if you've got related tables.

Similar to this question: Reorder / reset auto increment primary key

---

Why would one need this?

You ask why not?

because lets say your table is users - now when you rearrange keys here, the "posted_by" area in posts (for example) will now show a diffrent user... this is a bad practice.

---

It is possible to do but It will hurt your database. If you are going to do this, you need to understand EXACTLY what you are doing and how it will affect everything in your database (related tables, replication, backups, etc). Here is a link with information on how to do it but I strongly recommend you reconsider.

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

---

You can assign a value to an auto_increment column as long as the inserted/updated value does not violate uniqueness. Also as others have advised you have to make sure that you do not mess up any relationships. If you're doing this because you want to display the PK somewhere in your UI you should either alter the table to have a DisplayID field or write a query that returns a consecutive iterator after ordering by PK.