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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
