Resetting Identity Seed/PK''s
-
Hi Guys 'n' Gals
Newbie here
I have a problem that hopefully one of you guru's can answer for me. it's concerning the id of a PK.
Let me explain....
....sometimes when inserting a new record into a table an expected error occurs, usually in early development/testing. When this error occurs the record is not inserted, which is great and expected, however, the id/pk column value skips a number on the next insert.
PK settings - col_id int identity (1,1) primary
So records 1-16 enter fine, then error on next insert (record 17), when next record added the id is 18
What I need from you please.
Can you tell me how to avoid this occuring in the future, what do I need to include in my coding? Other than a transaction (which I don't want to do) I can't think of another way to do this - that's just newbie talking here
Do you have an example script I can run against a table to reset the identity column. This then raises another question, if the PK column is a FK in another table, will the cascade UPDATE setting on the linked column, handle any changes to the identity column value?
-
Okay, I've been playing around with fixing a table and came up with this so far. Haven't tested on a PK linked to a FK yet.
Any comments folks, am I going up the right path or am I going to fall over the edge of the precipice to my doom
DECLARE @CURRENT_ID INTSET @CURRENT_ID = (SELECT IDENT_CURRENT('tbl_mytable'))
IF (SELECT col_mycol FROM tbl_mytable WHERE col_mycol = @CURRENT_ID) IS NULLBEGIN
DECLARE @CURRENT_ID_LAST INT, @CURRENT_ID_FIX INT, @CURRENT_ID_DIFFERENCE INT
SET @CURRENT_ID_LAST = (SELECT TOP 1 col_mycol FROM tbl_mytable ORDER BY col_mycol DESC)SET @CURRENT_ID_DIFFERENCE = (@CURRENT_ID - @CURRENT_ID_LAST)
SET @CURRENT_ID_FIX = @CURRENT_ID - @CURRENT_ID_DIFFERENCE
PRINT 'RECORD NOT FOUND - INITIATING FIX!'PRINT 'CURRENT INCORRECT LAST IDENTITY - ' + CAST(@CURRENT_ID AS VARCHAR(32))
PRINT 'CURRENT REAL LAST IDENTITY - ' + CAST(@CURRENT_ID_LAST AS VARCHAR(32))
PRINT 'CUURENT ID DIFFERENCE - ' + CAST(@CURRENT_ID_DIFFERENCE AS VARCHAR(32))
DBCC CHECKIDENT('tbl_mytable', RESEED, @CURRENT_ID_FIX)PRINT 'FIX COMPLETE!'SET @CURRENT_ID = (SELECT IDENT_CURRENT('tbl_mytable'))PRINT 'CURRENT FIXED LAST IDENTITY STORED - ' + CAST(@CURRENT_ID AS VARCHAR(32))
END
ELSE
BEGIN
PRINT 'RECORD FOUND' -- or something else
END
-
I guess I need to ask the question... IF you understand that the ID may skip numbers for various reasons "What is the burning desire to NOT skip numbers"?
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Hi
Your question is a good one.
This will only be a problem whilst under development, if it happens on the production server then so be it, but I would rather it didn't and that was one of the questions I posted, how to stop this happening in the first place. I personally would want to make sure all tables are 100% before pushing to production, including all pk's be sequential and not having any gaps.
Do you just live with this then AJ, is this the accepted norm? Am I being too particular about this? Is it a case of accept it and forget it?
It might be a waste of time, but a good learning excercise nonetheless.
-
If the missing ids are causing problems with your compagny, then by al means try to correct hit. Like in my case if we have a missing WorkOrder then they assume that we are not declaring all our revenues or something like that. But otherwise I just wouldn't worry about that as it's a number that none of the users will ever see/use so it just doesn't matter to the system.
-
There is usually a better choice for PK's than an identity integer. If there is not a single column that is a natural PK then use a composit PK. First Name, Last Name, MI, Date hired makes more sense for a PK for employee records than does a random (from the employees viewpoint) number assigned by the computer.
Mike
-
Might seem a silly question but - you go live and you have your neatly ordered, no gapped PK's.
Say you have PK's 1 to 1 million.
For whatever reason, number 9501 has to be deleted - are you going to re-order the tables again (PK's and FK's) ?
Or do you create a system that deals with gaps in your PK's?
Just a thought......
Have fun
Steve
We need men who can dream of things that never were.
-
Hi Guys
Thanks for the comments posted, I really appreciate your thoughts
Remi >> I don't have that problem here where an id has to be accounted for, and by the sounds of it, I'm glad I don't have to.
Michael >> I understand about the composit PK, I've used them in the past where appropriate and definately resolves the issue. Composit PK's are not the answer on a majority of the tables, especially when needing to reference the PK from another table, they're just impossible some of the time due to having to include all composit PK fields as a FK.
I think this is really about my frustration at the DB allowing the PK/identity to be incremented when an insert error occurs and it's failure to rollback. I just don't want gaps in my data, it's not a lot to expect really, however, I'm getting the feeling that it's something I'll have to learn to live with.
-
Good point Steve, on production it's acceptable without a doubt on deletion of a record. I'm really talking about inserts in my dev system before pushing to production, that's all.
Maybe I've got a bad case of detailitis
-
If you are going to use an automatically created surrogate key (and many would argue you should not), you should definitely not expose it as having some meaning. If it makes any difference whether or not there are gaps then you are using the wrong structure and should probably redesign.
-
Chris >> It doesn't make any difference to the db at all, just to me
-
My 2 cents - you save a lot of energy and heartache by simply ignoring the gaps. They do no harm. 🙂
The philosophy I like to follow is like Chris': the key is either a "real" key, having some meaning outside the database, OR a surrogate key, automatically generated using Identity, which has NO meaning outside the DB. The first would be something like a true "order number," the second a simple, sequential integer.
To me it makes sense not to mix those -- that is, to use Identity to make a surrogate key but also associate some meaning to the number and expose it outside the internal workings of the database. If you do mix the two, then you immediately bump into this issue of why are there gaps, or what order are the records stored in in the database, which are bothersome but not actually productive to solve.
-
Hi Merrill
I totally understand what you and Chris are saying about real and surrogate keys. This question of mine was soley for the cleanup of gaps caused by insert record errors on the dev box. Maybe I shouldn't have said I have a 'problem' because I think that I've probably positioned this thread incorrectly, more like 'I wonder if this can be done'. I don't have a design problem, an identity problem, I have a detailitis problem, wanting to deliver to production the most perfect db I can, that's all.
Look at it this way, if you running loads of code tests, commenting bit and pieces out here and there, when you've finally got a full running version with no bugs, do you leave the commented out test lines in your code, or do you go in and clean them out and apply appropriate comments to the code, see where I'm coming from, like walking backwards, clearing your tracks. I'm looking at those gaps as being my residue of buggy tests and would rather not have them left behind.
If you're all telling me that it's normal practice to move from dev to production with the pk gaps included then fine, I'll learn to live with it - no problemo - honest.
-
I don't have a design problem, an identity problem, I have a detailitis problem, wanting to deliver to production the most perfect db I can, that's all.
Why is having gaps in the sequence anything less than perfect?
If you're all telling me that it's normal practice to move from dev to production with the pk gaps included then fine, I'll learn to live with it - no problemo - honest.
It is more or less normal practice yes, or at least it is not normal practice removing gaps. But that is depending on how people move from dev to production. Should you really have data in dev that is moved to production? And especially with automatically generated surrogate keys?
-
If you know which identity values have not been used or were deleted you can use the SET IDENTITY_INSERT statement, and insert an explicit value into the identity column. Check BOL.
HTH mike
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply