Unique Random Number?

[Drew Lane]

I would like to create a unique random number each time a new record is inserted into my database. The number should be different from any number that is already in the database.
I want to use this number as a customer account number, so I'd rather it not be sequential.
Any ideas how to go about this?
Is this something that is available as part of most databases?
Thank you,
Drew

[Syam Veerakumar]

There should be a way to store unique numbers in data base,if you Oracle is your database then use sequence to insert unique numbers in database.
[ July 23, 2002: Message edited by: Syam Veerakumar ]

[Dave Vick]

Drew
As Syam said, most databases have a way to do this automatically. In most of them you do it wh you set up the table by creating a columnn defined as auto_increment or something like that. then when ever you insert a new record into the database it'll increment the last record number of that column.

[Drew Lane]

Maybe I didn't explain this well enough.
I understand how to create unique numbers by using the primary key and auto increment, etc. But this is not what I want.
I want to issue unique numbers to customers that are non sequential. These numbers will be used as an id to log into my web application.
For example:
5 unique random numbers between 100000 and 110000
102753, 103461, 103608, 105179, 108069
For security reasons, the numbers can't be sequential, nor can I use an email address.
Any thoughts?
Thanks,
Drew

[Syam Veerakumar]

For this, get unique numbers from a sequence and add some random numbers generated automatically with that, so you'll get unique and a non sequence number.This is just an idea you can use your own logic.

[Michael Matola]

I want to issue unique numbers to customers that are non sequential. These numbers will be used as an id to log into my web application.
...
For security reasons, the numbers can't be sequential, nor can I use an email address.
I've done something similar once (not in Java).
1. Generate a random number.
2. Select against the table to make sure that the random number doesn't already exist on the table. If it does, generate a new random number (go back to step 1).
3. Insert row with the random number.
4. Commit
In my case, I was working in a nonmultithreading, nonmultitasking environment, so I was confident that between steps 2 and 3, nothing else could have inserted that same number into the table.
Also, you probably want to have an index on the random number column for performance reasons. As the table gets larger, step 2 can get costlier.
Also, don't forget step 4 -- committing after each insert. Otherwise, you could have a potentially matching random number in the insert/commit buffer that would not be detected by the select in step 2.
Also, instead of the select test in step 2 you could just insert without the check if you have a unique constraint set up on the random-number column and catch the unique constraint error. (This was not an option for me in the stuff I was working on.)

[Drew Lane]

Thanks Michael, this is more what I had in mind...
Now one more idea I have to solve my problem would be to take the key and multiply it by some number and add a value to it, etc.
I know this won't create a random number per se, but perhaps it would be unique enough to provide minimal security.
I suppose someone might be able to figure out the formula I used, but it seems few and far between.
Would there be other problems using this method?
Regards,
Drew

[Andres Gonzalez]

One evil suggestion that I got from a rancher here. Use
long entryDate = System.currentTimeMillis();
I was having a problem like this. I was uploading files to my server and want these file names to be unique. It is not the best solution, i guess, but I was stuck with this 'simple' problem for hours that I decided to use this 'trick'. now is working ok..
not the best solution but it might help you.
cheers

[Rob Ross]

The current time is usually a good enough seed for the random generator. The only ways you would ever get a duplicate start seed are:
1) You reset the date/time clock to a previous date and with great skill and luck also happen to call the method at the exact same millisecond instant that you did previously. Good luck. And if you do that, please let me know so I can fly you out to Las Vegas and you can roll dice for me.
2) You keep running your program until some time in the year 584million, and THEN hope you check the time at the exact same moment you did 584 million years ago.
Other than these, it's a pretty slim chance you're going to get the same seed value, so using the system time really isn't such a bad thing after all.