How to generate a unique and random number for a primary key in sql

Question

I am working on a requirement where I need to generate a unique(non-repeatable) and random number(unpredictable) with atleast 10 digits. I tried SELECT FLOOR(RAND() * 9999999).. but the uniqueness cant be guaranteed with this. About 20k values will be inserted per month approx.

Also I want to increment an int column of my table(number_of_hits) for every entry inserted... I am using spring boot to insert values into table. Tried number_of_hits int AUTO_INCREMENT while creating the table but later got to know this will only work on primary key.. Thanks in advance.!

_Be aware that the answers here may or may not be applicable to SQLs other than H2._ — Rick James, Jul 12 '21 at 00:00

Neville Kuyt · Answer 1 · 2018-07-04T10:21:58.477

2

The easiest solution is to use a UUID datatype (which can be populated automatically), and convert to INT when you need it.

A UUID is a 128-bit integer and can be converted to BigInteger; it's usually stored as a hexadecimal value, which may appear to be a string.

edited Jul 04 '18 at 10:21

answered Jul 04 '18 at 09:31

Neville Kuyt

29,247
1
37
52

Thanks @Neville... But this gives alphanumeric key.. and I need a key which consists of only numbers.. – pa_One Jul 04 '18 at 09:48
The key is commonly displayed as alphanumeric digits, but it's just bytes. You can just as easily use it as a number. – iCodeSometime Jun 01 '22 at 17:46

Tom · Answer 2 · 2018-07-04T09:24:20.807

0

Use newid() function.This will creates a unique value of type uniqueidentifier.

NEWID()
SELECT NEWID()

You can use like this.

INSERT INTO mytable (column1) VALUES (NEWID())

edited Jul 04 '18 at 09:24

answered Jul 04 '18 at 07:03

Tom

1,343
1
18
37

Hi @Tom thanks for the reply.. can you be more specific, should I do it while creating a table ? sorry (sic) I am new to sql... – pa_One Jul 04 '18 at 07:25
insert into mytable (column1) values (NEWID()) – Tom Jul 04 '18 at 07:27

score 0 · Accepted Answer · answered Jul 05 '18 at 10:12

I have gone with the below solution and it works fine with millions of records too.. Thank you all for your answers.

      Calendar cal = Calendar.getInstance();
      long currentTime = cal.getTimeInMillis();
      long Max = 9999999999999L;
      long Min = 1000000000000L;
    long range = Math.abs((long) (Math.random() * (Max - Min)) + Min);
    long id = Math.addExact(currentTime, range);
    String uniqueID = createUniqueID(id);
    boolean isRepeated = urlShortenerRepository.existsByShortUrlKey(uniqueID);
    while (isRepeated) {
        range = Math.abs((long) (Math.random() * (Max - Min)) + Min);
        id = Math.addExact(currentTime, range);
        uniqueID = createUniqueID(id);
        isRepeated = urlShortenerRepository.existsByShortUrlKey(uniqueID);
    }

Peter O. · Answer 4 · 2019-05-16T05:35:49.797

Since you have chosen to use Java code to generate a unique identifier, I want to say that you should generate a unique identifier that combines a "unique" part and a "random" part. Note that your current answer doesn't exactly meet the "unpredictable" requirement, since it uses Math.random(), which is not necessarily an "unpredictable" RNG.

The "unique" part can be a monotonically increasing counter, or it can be a number generated with a full-period linear congruential generator (which cycles pseudorandomly through all possible values in its period before repeating). I don't recommend timestamps alone since the risk exists of generating the same timestamp in rapid succession.
The "random" part is simply a random number generated with a cryptographic random number generator (which for Java is java.security.SecureRandom; use the "DRBG" implementation rather than "SHA1PRNG" if available). In general, the longer the random part is, the less predictable it will be.

Your current code is on the right track, but you should concatenate the two parts of the random ID (like they were strings) rather than adding an arbitrary offset to the current time (e.g., what if cal.getTimeInMillis() returns a number greater than Min?).

How to generate a unique and random number for a primary key in sql

4 Answers4

Linked