better approach to insert unique code to the database

Question

Just a thought. I want to insert unique code to the database. So let's say, my code would be from 0 to 9; 2-digit code. (just an example) And set the code field to unique.

I have here a loop flow.

This approach:

loop
  digit_code = generate_random_code()
  bool = insert_to_database(digit_code) // insert to db; returns boolean
  if bool // if successfully inserted
    break
endloop

OR this:

rows = get_all_code() // retrieves all data from db
digit_code = generate_random_code()
loop rows
  if digit_code is not in rows
    insert_to_database(digit_code)
    break
endloop

So basically, the task is simple, generate unique code for each transaction. Now what would be a better approach? Repeat insert until success or go to db once (to get all data) then do an insert. Or if you have any great suggestion. I'm happy to take them. Just wondering. Thanks.

Why not use a db auto generated id? Then you don't have to repeat inserts or query for an unused id. — cmd, Dec 02 '13 at 04:01
Another option would be to use a UDID right? which if there's one machine would mean no fear of collisions but given the choices, the second for sure makes the most sense. The db will figure out whether it's seen the key just looking at the index. — Rob, Dec 02 '13 at 04:02
I must have confused you. Sorry about that. It's not intended for the ID. Actually, the code is a 4-alphanumeric @cmd. — Boy Pasmo, Dec 02 '13 at 04:03

score 1 · Accepted Answer · edited May 23 '17 at 10:31

The first choice is fail dependent and it is implied that the failure occurs because it looked to see if the key already existed. In this limited scenario, the 2nd option wins because all the work is shown and no work is implied.

A better choice in the real world is to generate a UUID.

http://en.wikipedia.org/wiki/Universally_unique_identifier

or a timestamp

how to generate a random timestamp in java?

or utilize the database's sequence or auto-increment abilities.

better approach to insert unique code to the database

1 Answers1