CODEWITHSUNDEEP
c#sqldatabaseprimary-key
TalhaDX
TalhaDX

Reputation: 1

How to Auto increment and auto change in database column value

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.

Upvotes: 0

Views: 233

Answers (2)

Awtszs
Awtszs

Reputation: 341

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.

Upvotes: 0

tgolisch
tgolisch

Reputation: 6734

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.

Upvotes: 1

Related Questions