There a way to have primary key ids without any gaps-CodePudding

I'm creating an application with Java Spring and Oracle DB.

In the app, I want to generate a primary key value that is unique as well as ordered and without gaps: 1,2,3,4,5 instead of 1,2,5,7,8,9.

I've at one point used max(id) 1 to get the maximum value of the id, and the id of the next/current transaction. However I know it isn't perfect in the case of concurrency with multiple users or multiple sessions.

I've tried using sequences, but even with the ORDER tag it could still create gaps with the possibility of a failed transaction.

REATE SEQUENCE num_seq 
START WITH 1 
INCREMENT BY 1 
ORDER NOCACHE NOCYCLE;

I need there to be gapless values as a requirement, however I'm unsure how it's possible in the case of multiple users/multiple sessions.

CodePudding user response：

Don't do it.

The goal of primary keys is not to be displayed on the UI or to be exposed to the external world, but only to provide a unique identifier of the row.

In simple words, a primary key doesn't need to be sexy or good looking. It's an internal identifier.

If you are considering the idea of having serial identifier, that means you probably want to display it somewhere or you want to expose it to the external world. If that's the case, then create a secondary column (also unique) that serves this "public relations" goal. It can be automatically generated, or updated at leisure without affecting the integrity of the database.

It can also be generated by a secondary process that runs in a deferred way (e.g. every 10 minutes) that finds all the "unassigned" new rows, and gives them the new number. This has the advantage that is not vulnerable to concurrency.