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?

5 Answers 5


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.

  • +1 take the pain of moving to a larger key space. Consider UUIDs if you think 64 bits isn't enough.
    – Gary
    Sep 3, 2011 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. Sep 4, 2011 at 13:56

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?

  • 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 Sep 2, 2011 at 9:55
  • 1
    @shinynewbike: Do not ever reset that surrogate PK if you want to avoid more headaches.
    – Falcon
    Sep 2, 2011 at 10:03
  • 3
    That is certainly the industry standard advice. Do not reuse. It's standard because it's almost always right. Sep 2, 2011 at 10:09

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.

  • +1 for notifying managers. It's typical for a company to have undocumented usage of data.
    – JeffO
    Sep 2, 2011 at 14:13

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.


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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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