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)

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.

@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 WhitlockSep 4 '11 at 13:56

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.