What are the strategies for dealing with Numeric Sequence Primary keys once you hit the data type limit...

Question

Consider the following sample table in MySQL:

CREATE TABLE transactions 
(
    transId BIGINT NOT NULL AUTO_INCREMENT,
    transDate  DATETIME NOT NULL,
    transTotal  DECIMAL(10,2),
    PRIMARY KEY (transId)
);

This table is used in high volume operations i.e. lots of INSERTS. You will eventually reach the maximum limit of transId. (Of course in reality BIGINT offers pretty much larger range.)

What are the possible strategies to prevent this and not worry about roll-over issues that would break your application.

• Would UUID as primary be the solution?

Answer 1

Unsigned bigint is limited by 18446744073709551615. Assuming you have 10 000 000 000 inserts per day - you will reach that limit only within 59 years

Answer 2

What are the possible strategies to prevent this and not worry about roll-over issues that would break your application.

Sell your application for a couple of billion dollar and you don't have to worry about anything.