Create Sequence Oracle SQL random primary keys

Question

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?

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
– Cedric Simon, Commented Mar 14, 2014 at 21:26
@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'Tatsu, Commented Nov 5, 2014 at 21:40

Jakub P · Accepted Answer · 2014-03-14 21:23:24Z

1

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
;

answered Mar 14, 2014 at 21:23

Jakub P

5515 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Cedric Simon Over a year ago

Primary keys must be unique. Where is the uniqueness check here?

Jakub P Over a year ago

...MINUS ( select SIN from people ) You exclude from generic sequence the numbers already existing in people.

Jeffrey Kemp Over a year ago

Unfortunately this will still allow collisions, as it cannot query rows that have been inserted by other sessions but not yet committed.

user2672165 · Accepted Answer · 2014-03-15 10:41:00Z

0

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.

edited Mar 15, 2014 at 10:41

answered Mar 14, 2014 at 21:07

user2672165

3,05921 silver badges29 bronze badges

1 Comment

Erk Over a year ago

Maybe security by obscurity is the reason for a random primary key? Which, of course, is a bad idea in itself...

Collectives™ on Stack Overflow

Create Sequence Oracle SQL random primary keys

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related