How to get a unique alphanumeric based on a unique integer

Question

My webapplication has a table in the database with an id column which will always be unique for each row. In addition to this I want to have another column called code that will have a 6 digit unique Alphanumeric code with numbers 0-9 and alphabets A-Z. Alphabets and number can be duplicate in a code. i.e. FFQ77J. I understand the uniqueness of this 6 digit alphanumeric code reduces over time as more rows are added but for now I am ok with this.

Requirement (update) - The code should be at least of length 6 - Each code should be Alphanumeric

So I want to generate this Alphanumeric code.

Question

What is a good way to do this?

• Should I generate the code and after the generation, run a query to the database and check if it already exists, and if so then generate a new one? To ensure the uniqueness, does this piece of code need to be synchronized so that only one thread runs it?
• Is there something built-in to the database that will let me do this?

For the generation I will be using something like this which I saw in this answer

char[] symbols = new char[36];
char[] buf;
    for (int idx = 0; idx < 10; ++idx)
        symbols[idx] = (char) ('0' + idx);
    for (int idx = 10; idx < 36; ++idx)
        symbols[idx] = (char) ('A' + idx - 10);
public String nextString()
{
    for (int idx = 0; idx < buf.length; ++idx)
        buf[idx] = symbols[random.nextInt(symbols.length)];
    return new String(buf);
}

Answer 1

Since it's a requirement for the shortcode to not be guessable, you don't want to tie it to your uniqueID row ID. Otherwise that means your rowID needs to be random, in addition to unique. Starting with a counter 0, and incrementing, makes it pretty obvious when your codes are: 000001, 000002, 000003, and so forth.

For your short code, generate a random 32bit int, omit the sign and convert to base36. Make a call to your database, to ensure it's available.

You haven't explicitly called out scalability, but I think it's important to understand the limitations of your design wrt to scale.

At 2^31 possible 6 char base36 values, you will have collisions at ~65k rows (see Birthday Paradox questions)

From your comment, modify your code:

public String nextString()
{
    return Integer.toString(random.nextInt(),36);
}