How to make primary key serially?

Question

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

This is a great way to destroy the integrity of your database. — Ignacio Vazquez-Abrams, May 11 '11 at 12:15
This will help http://stackoverflow.com/questions/740358/mysql-reorder-reset-auto-increment-primary-key/5437720#5437720 — Anshul, Jul 14 '11 at 06:48

score 3 · Answer 1 · answered May 11 '11 at 12:15

3

You don't need this. Just believe me!

answered May 11 '11 at 12:15

Vadim

5,154
2
20
18

+1 from me. You should NEVER want such behavior or need it. Do not touch primary keys, believe Vadim :) – Michael J.V. May 11 '11 at 12:20
if you want to get the serial numbers list - you should create sequence. But as i remember mysql don't support sequences naturally, so you can create it with separate transaction safe InnoDB table. – Vadim May 11 '11 at 12:31

score 1 · Answer 2 · edited May 23 '17 at 11:55

1

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

edited May 23 '17 at 11:55

Community

1
1

answered May 11 '11 at 12:17

m4rc

2,932
2
22
29

score 1 · Answer 3 · answered May 11 '11 at 12:18

1

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.

answered May 11 '11 at 12:18

fingerman

2,440
4
19
24

score 0 · Answer 4 · answered May 11 '11 at 12:22

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

score 0 · Answer 5 · answered May 11 '11 at 12:22

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.

How to make primary key serially?

5 Answers5

Linked