Re: Create on insert a unique random number

Thanks for all of your input. It appears that the best way to do this
is to create a default random number in the primary id field in the
table definition and then return that value after insert. If an
exception occurs because of duplicates I will simple perform the same
insert statement again. I doubt there would be many duplicate hits if I
use a really large number.

Why use a random number as a primary key? Security via obscurity.

I build web applications for a living. In most of my applications it is
preferable to use a random primary key. Why?

Example:

I built a web application called the Form Builder. It allows
individuals to create web forms. After a user is done building their
web form the tool provides a URL for the user to access the form.
Obviously the URL has the random ID of the form in it. Most of the
forms created with this tool can be accessed and filled out by the
general public.

So why not use a sequential number? So if I used a sequential number
then a student or outside individual could easily change the number in
the URL to see what other forms there are. It is not that they don't
have access to the forms but they should not be messing with them if
they really don't have a reason to. So by using a very large random
number the users filling out a form cannot easily guess what another
form ID is.

Does that make sense? I have at least a dozen tools that I prefer to
use this approach on.

This is not a security approach. It is more about not giving obvious
access to people that want to mess around.

Thanks,

Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Vivek Khera
Sent: Tuesday, March 18, 2008 12:57 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Create on insert a unique random number

On Mar 18, 2008, at 1:03 PM, Campbell, Lance wrote:

> The field n is not random but is sequential. Is there something I
> should do to make the serial number random?

Depending on your "randomness" need, you can alter the increment of
the sequence so it changes by a different amount than "1" on every
increment, though it will still be constant increment.

You need to specify what the purpose of it being random is, then you
might get more useful responses. Does it need to be random for some
security purpose? If so, describe the level of security,
specifically, against what threat are you defending?