Thread: Random Unique Integer
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I have a table T1. It contains a field called F1. Is there a way for me to set the table T1 up suchthat F1 can be populated with a random integer such that F1 is a unique integer? I would rather not create a storedprocedure.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Thanks,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Lance Campbell</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Project Manager/Software Architect</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial">Web Services at Public Affairs</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">University</span></font><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">of Illinois</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial">217.333.0382</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">http://webservices.uiuc.edu</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><spanstyle="font-size: 12.0pt"> </span></font></div>
On Thu, Jun 14, 2007 at 08:08:26AM -0500, Campbell, Lance wrote: > I have a table T1. It contains a field called F1. Is there a way for > me to set the table T1 up such that F1 can be populated with a random > integer such that F1 is a unique integer? What problem are you trying to solve? The solution might depend on what you're trying to do. -- Michael Fuhr
I have a web application that is used to create web surveys and web forms. Users can create any number of surveys or forms at any time. The primary key on one of my tables defines the ID for any given form or survey. I do NOT want the number sequential. Since I use the number in the web URL as a parameter, I don't want people guessing what survey or form is next. By creating a very large random number the users cannot easily guess another form or survey's ID. This number is not designed for security but to keep people from being nosey and poking around easily into someone else's form or survey. Can a table have a primary key that is randomly generated? 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: Michael Fuhr [mailto:mike@fuhr.org] Sent: Thursday, June 14, 2007 8:26 AM To: Campbell, Lance Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Random Unique Integer On Thu, Jun 14, 2007 at 08:08:26AM -0500, Campbell, Lance wrote: > I have a table T1. It contains a field called F1. Is there a way for > me to set the table T1 up such that F1 can be populated with a random > integer such that F1 is a unique integer? What problem are you trying to solve? The solution might depend on what you're trying to do. -- Michael Fuhr
On 14.06.2007, at 22:40, Campbell, Lance wrote:
> I have a web application that is used to create web surveys and web
> forms. Users can create any number of surveys or forms at any time.
> The primary key on one of my tables defines the ID for any given
> form or
> survey. I do NOT want the number sequential. Since I use the
> number in
> the web URL as a parameter, I don't want people guessing what
> survey or
> form is next. By creating a very large random number the users cannot
> easily guess another form or survey's ID. This number is not designed
> for security but to keep people from being nosey and poking around
> easily into someone else's form or survey.
>
> Can a table have a primary key that is randomly generated?
you could do it like this:
test=# create table testsurvey (id_survey serial primary key, survey
text);
NOTICE: CREATE TABLE will create implicit sequence
"testsurvey_id_survey_seq" for serial column "testsurvey.id_survey"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"testsurvey_pkey" for table "testsurvey"
CREATE TABLE
test=# insert into testsurvey (survey) values ('first');
INSERT 0 1
test=# insert into testsurvey (survey) values ('second');
INSERT 0 1
test=# insert into testsurvey (survey) values ('third');
INSERT 0 1
test=# select *, md5(id_survey) from testsurvey;
id_survey | survey | md5
-----------+--------+---------------------------------- 1 | first | c4ca4238a0b923820dcc509a6f75849b 2
|second | c81e728d9d4c2f636f067f89cc14862c 3 | third | eccbc87e4b5ce2fe28308fd9f2a7baf3
(3 rows)
so you actually show out only the PK id-s md5 hash
test=# create index idx_survey_md5 on testsurvey (md5(id_survey));
CREATE INDEX
test=# select * from testsurvey where md5(id_survey) =
'c81e728d9d4c2f636f067f89cc14862c';
id_survey | survey
-----------+-------- 2 | second
The hashing algorithm you chose depends on the amount of rows you
expect there.
And also how difficult cracking it needs to be.
In case you have many rows and you think collision checking is
necessary you could
store the calculated hash in a new column. for replication management
purposes
its better to keep the PK as serial / bigserial.
Example: 100K surveys,
hashtext(id) : 4B int
100 000 / 256 ** 4 = 42949 (1:42949 chance that the next number will
lead to a valid survey entry)
keep the hashed pk value in a separate row as you will deplete
1/42949-th of the hash space with 100K rows
peace,
Kristo