Create Sequence Oracle SQL random primary keys

0
votes

I cant seem to find any information regarding create sequences.

If there is an existing database and a table PEOPLE has primary key SIN, if I wanted to generate Primary Keys. How would I do this given that the primary keys are RANDOM. Essentially I was thinking this would be easy if I just have to count the number of rows and set that as my minimum in my create sequence. However, given that the primary keys in the table are random how would I implement the create sequence? If I just want to add new primary keys that have values incremented from the previous inserted values.

Or does create sequence check this automatically for me?

2
sqldatabaseoracle
What is the added value of a random over a sequence number? If random, you would have to check uniqueness before using it, it looks like a resource waste to me. I understand the need of a random value for a common field, not for a PK. - Cedric Simon
@CedricSimon, the standard reason for a random key is to reduce the ability to URL hack a site. In the early days of the web you could do things like hijack shopping card software by editing a cookie to a value one lower or higher than your assigned value. Session keys were commonly an AUTOINCREMENT column in a MySQL database. Similarly imaging going to your bank and downloading your bank statement, then edit any number in the URL to be one less and finding you can download someone else's bank statement. (This still happens!) - Ven'Tatsu
@Ven: Thanks for your interesting comment. - Cedric Simon

2 Answers

1
votes

I found the below working. The point is to generate whole sequence, exclude existing primary keys and finally pick a random value from what's left. Additionally You have to pack this into "BEFORE INSERT" trigger.

select rn from (
select 
  row_number() over (order by dbms_random.random) as mx, rn 
 from (
  select rownum  as rn 
    from dual
  connect by rownum <= 10
  minus
 ( select SIN from people ) 
   )
     ) where mx = 1
;
0
votes

You would need execute immediate as far as I know.

declare
    n integer;
begin
   select max(sin) + 1 into n from people;
   execute immediate 'create sequence my_seq start with ' || n ||' increment by 1';
end;

Edit: I thought this question was to create a sequence that is to be unique given the existing random records existing. As Cedric says I do not understand the reason to have primary keys random.