Deleting rows and later reusing the Identiify value

Question

Deleting rows and later reusing the Identiify value

Barkingdog

SSCoach

Points: 18670
More actions
December 5, 2008 at 4:25 pm

#202476

We have a table like this
ID Val
1 Data item 1
2 Data item 2
3 Data item 3
4 Data item 4
5 Data item 5
where ID ia a primary key (referenced by other tables) as well as an Identity field. Suppose we delete record 2 so the table
now looks like this:
ID Val
1 Data item 1
3 Data item 3
4 Data item 4
5 Data item 5
We want the next record added to this table to take the place of the deleted recrod:
ID Val
1 Data item 1
2 New Data item 2
3 Data item 3
4 Data item 4
5 Data item 5
and the next record after that oen added like this:
ID Val
1 Data item 1
2 New Data item 2
3 Data item 3
4 Data item 4
5 Data item 5
6 Data item 6
In short, in this case we want the primary key of the deleted record to be reused and all following
records to use the the value generated by the Identity field. The actual table could contain multiple
"deleted" rows and the number of them determine when we restart the numbering based on the Identity
field.
Any suggestions how to do this appreciated.
TIA,
barkingdog

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Very simple: don't.

Identities should not be reused - and, you really shouldn't care what the actual number is. If you really care about what the number is, then you probably shouldn't be using an identity in the first place.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

Barkingdog... can you please explain WHY you want to reuse those numbers? What you want to do isn't impossible, although you couldn't designate the column to be an identity column. But there may be much better answers if you are just using them to designate a sequence.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

If you're using it as a Primary Key, then I'm on board with Jeffrey Williams - don't do it. This sets up "evil twin" scenarios and/or relational integrity problems which will make your head spin. Nothing quite like finding you had an unenforced "relation" that now matches something that USED to be related to an older RowID 2, to a NEW completely different RowID 2. And it's an invoicing scheme, so someone just got billed for something they didn't get...That way leads to much pain, which of course leads to the Dark side. Find another way young padawan....

If you're using it for some other purpose than PK, then Bob is onto something (there are better ways to do that most of the time - so what's the goal?)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

Heh... one religous battle coming up... ready?

NEVER DELETE! NEVER REUSE PK's!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

in the interests of database\referential integrity don't re use them. As stated, if you're needing to re use the column it probably shouldnt be a PK.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 6

As others already recommended - don't reuse the delete primary key. It will only lead to integrity problems, but I am curios to know why you want to reuse the deleted values. Can you write a bit about your situation? Maybe we will be able to suggest a better alternative.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Jeff Moden (12/6/2008)
Heh... one religous battle coming up... ready?
NEVER DELETE! NEVER REUSE PK's!

I have to say - we haven't managed to trigger a full-out religious war on any topic in some time now. Very strange...:):w00t:

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

No heretical rebuttals from me. I just want to know if they are using that column to enforce a sequence.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Barkingdog SSCoach Points: 18670 More actions · Answer 9

The idea was to always have the primary key sequence like this : 1,2,3,4.... (a psychological, not computer, issue.)

WELL, after reading the replies to the question I have scrapped the idea as being thoroughly bad and wil not attempt to implement it.

Thanks to everyone for their guidance.

Barkingdog

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 10

Good plan. Psychological issues are better served by counseling 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

... heh... or some very high velocity pork chops fired at point blank range. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 12

By trebuchet or air cannon?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Jeff Moden (12/9/2008)
... heh... or some very high velocity pork chops fired at point blank range. 😉

Of course - close encounters with high velocity pork chops (whether fired from slingshot, trebuchet or whatever) might result in needing the previously mentioned counseling....:)

"Traumatic flying pork stress disorder"....:D:sick:

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 14

:w00t:

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills