0

I have a SQL table that stores different data. And the primary key has integer value that is incremented 1 one new data is entered. As long as we keep on adding it works fine. But when we delete any center value or ending value it causes problems.

i.e for example I have added 5 rows in the table. And the column sr_num holds value of 5. And when I delete the 4th record the sr_num column remains like this: 1,2,3,5.

I want it to be 1,2,3,4 as soon as I delete the 4th entry, I want the 5th one to take 4th position and same number as well.

It must to happen to all.

Andrea
  • 11,801
  • 17
  • 65
  • 72
TalhaDX
  • 1
  • 5
  • 5
    Are you **SURE** you want something like that? It means you have to **manually** update that table. Manually update all tables with a relationship with that one (and so on, cascading). Moreover your index will be invalidated. Can I ask the **reason for that**? It's not right usage and meaning of a primary key, if you want a progressive index just use `ROW_NUMBER()` in your `SELECT`... – Adriano Repetti Jan 19 '14 at 16:52
  • 1
    If you delete a row and put another one with the same key in its place, that will also cause problems. You now have two logical rows for the same key. Keys should not be reused after they have been observed by someone else (a user). – usr Jan 19 '14 at 17:06
  • See this: http://stackoverflow.com/questions/2214141/mysql-auto-increment-after-delete?rq=1 – dursk Jan 19 '14 at 17:50
  • 2
    This is a **HORRIBLY BAD** idea! Imagine: you have a system where suddenly your serial numbers / id's change...... ***HORROR!*** Seriously: don't do this! First of all, it's compute and time intensive, it's very hard to get this right in a busy system, and it's a really really HORRIBLY bad idea to have numbers change in your system! This makes it **IMPOSSIBLE** to track your items in the database....... again: **DON'T DO IT!** – marc_s Jan 19 '14 at 17:53

2 Answers2

1

No. That is not what your primary key is used for. It is only for logical reference, to allow for uniqueness. You should mentally ignore the fact that it uses an integer. @Adriano and @marc_s are both correct. Let go of the idea that you could/should renumber your primary key values. There are some rare occasions when you might consider it, but this is not one of those rare occasions.

Instead, you could set up a query (or view) that uses ROW_NUMBER() in your query (as @Adriano mentioned). Then, you will have your consecutive numbers without messing with your primary key values. People usually refer to this as an ordinal column or simply Ord.

tgolisch
  • 6,549
  • 3
  • 24
  • 42
0

It is a bad Idea what you want to.

example: your sr_num has a foreign key to other table, once you update the sr_num you need to update the other table with the same value as sr_num too.

Awtszs
  • 323
  • 1
  • 8
  • 33