Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

We've hit an application issue with an Oracle table Primary Key exceeding the limits used by a Java Integer (2 ^ 32) and one of the suggestions is to reset this to 1000 and let it start again. OR change the code to use Java Long (2 ^ 64) instead.

This has a ripple effect since this key is used as ID by a lot of other systems as well. And there can be future duplication (in the far future)

Are there any suggestions on good and bad outcomes in this approach?

share|improve this question
add comment

5 Answers

up vote 16 down vote accepted

In my experience, non-technical people often think it's OK to recycle "keys" or "IDs" after a certain amount of time because "we'll know by the date which one it was". They don't understand the technical hurdles it can cause if you end up with a computer trying to figure out which one is which.

Having absolutely unique keys gives you some big advantages:

  • You can create a unique index to always be able to find it by the key
  • You can easily compare if two entities in memory are the "same" entity (just compare keys)
  • Error detection: if you have duplicate keys, that's an error. Fail early.
  • If you're using sequential keys, it's automatically sorted by first created.

That's why I would favour moving to a 64 bit key rather than trying to recycle 32 bit keys.

Now, I can imagine a system where it might be OK to recycle keys. If your data simply doesn't live long enough to ever have a duplicate key, then it might be alright. Unfortunately that means you're designing yourself into a corner though. What if someone wants to add an audit history later? They probably want to store the audit history by key.

share|improve this answer
    
+1 take the pain of moving to a larger key space. Consider UUIDs if you think 64 bits isn't enough. –  Gary Rowe Sep 3 '11 at 8:53
1  
@Gary Rowe - UUID/GUID's are excellent choices, especially if you want to allow new keys to be generated in remote disconnected systems and sync'd/uploaded later. However, incrementing IDs do have their advantages, if you're willing to give up that later flexibility. –  Scott Whitlock Sep 4 '11 at 13:56
add comment

Maybe I'm misunderstanding something. If you change the key to a long, why do you need to reset it to 1,000? Just keep going from 2^32 on.

Were these alternative solutions? Is this a class that holds objects that live for a very short time, so while it has very few rows at any particular time, it churns the primary key value rapidly?

share|improve this answer
    
No, this was an alternate - either move to Long or reset the PK. These are line items within a long list, hence they get new values very quickly –  shinynewbike Sep 2 '11 at 9:55
1  
@shinynewbike: Do not ever reset that surrogate PK if you want to avoid more headaches. –  Falcon Sep 2 '11 at 10:03
3  
That is certainly the industry standard advice. Do not reuse. It's standard because it's almost always right. –  David Schwartz Sep 2 '11 at 10:09
add comment

Reuse is bad. Don't do it. It will cause more problems not solve them. For instance suppose you have a report that went out and someone questions the data a month later. If the record was removed and replaced by something else, you are then not going to understand what they are talking about. If you havea database that is missing some FK constraints (I know you shouldn't but in the real world it happens), you may suddenly have data attached to the wrong record in queries. This is especially true since you say this data ripples out to other systems and you may not know what affect it would have on them. It makes it almost 100% likely that you would have some very bad effects. For instance in doing imports, we determine whether to update or insert data based on the client's Id number. If you reuse that you may be overwriting information incorrectly in their systems (they may not be deleting old record but marking them as inactive).

Some systems allow you to use negative numbers as the key, you can double the available numbers by doing so. There is also the possibility of using GUID id. But I personally would go for moving to Long.

Whether you decide to reuse numbers or go to using Long, you need to notify the managers of every other system that might get data from you to let them know. They may need to make adjustments to their import or reporting processes or database structure. We had some horrible data integrity issues to solve as a result of a client changing their ids without telling us.

share|improve this answer
    
+1 for notifying managers. It's typical for a company to have undocumented usage of data. –  JeffO Sep 2 '11 at 14:13
add comment

You'll have to talk to your other systems to go over the alternatives. I would expect moving to a 64 bit integer would be more likely to break them.

share|improve this answer
add comment

I assume that you have meant 2 ^ 31 = 2,147,483,647

I agree with the suggestions made so far...

1-Never circulate PKs

2-If you change the datatype, you need to coordinate this very well. This is not a trivial change.

There is something you may be willing to try. For an integer value the following is true:

-2,147,483,647-1 <= integer value <= +2,147,483,647

So far you have only used the positive part.

Why not use the negative part? you still have about 2,147,483,647 items to use without changing the data type of the column?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.