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?
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:
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.
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?
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.
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?
