CODEWITHSUNDEEP
phpmysql
user729022
user729022

Reputation: 71

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 result will be

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

Upvotes: 1

Views: 187

Answers (5)

Till
Till

Reputation: 3154

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.

Upvotes: 0

Bueller
Bueller

Reputation: 2344

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

Upvotes: 0

fingerman
fingerman

Reputation: 2470

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.

Upvotes: 1

m4rc
m4rc

Reputation: 3006

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

Upvotes: 1

Vadym
Vadym

Reputation: 5284

You don't need this. Just believe me!

Upvotes: 3

Related Questions