What is the proper size for a sequence-generated primary key?

Question

Currently, primary keys in our system are 10 digits longs, just over the limit for Java Integers. I want to avoid any maintenance problems down the road caused by numeric overflow in these keys, but at the same time I do not want to sacrifice much system performance to store infinitely large numbers that I will never need.

How do you handle managing the size of a primary key? Am I better off sticking with Java integers, for the performance benefit over the larger Long, and increasing the size when needed, or should I bite the bullet, go with Java Long for most of my PKs, and never have to worry about overflowing the sequence size?

Answer 1

It seems like the answer depends on how likely you are to overflow the Java integers with your data. And there's no way to know that without some idea of what your data is.

Answer 2

The performance benefit would be negligible, so my advice would be to go with the long keys. Having to deal with that down the road would likely be a major hassle.

Answer 3

I've always gone with long keys (number(18,0) in database) because they simply remove the possibility of this situation happening in pretty much all situations (extreme data hoarding style applications aside). Having the same data-type across all tables for the key means you can share that field across all of your model objects in a parent class, as well as having consistent code your your SQL getters, and so on.

Answer 4

It's a balance between the cost of storing and using Long integers, versus the likelihood of overflowing a 32-bit integer.

Consider that an unsigned 32-bit integer stores over 4 billion values. If you think you are going to average more than 1 new row every second in this table for the next 136 years, then you need to use a Long.

Answer 5

32 bit integers in java are signed integers, so only 2 billion. If for some reason, your SEQUENCE keeps jumping now and then, then there will be some gaps between your PKs.

It does NOT hurt to have a long (Remember that the Y2K problem happened because some COBOL developers thought that they will save some bytes in dates ??) :-)

Therefore, I always use Long.