​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
Creating surrogate numeric keys for tables the easy way 

A common feature of database systems is that certain tables require to have a unique numerical key which can be used to identify records. A lot of effort is sometimes expended using all sorts of convoluted coding schemes to ensure this happens. What a lot of people don't know is that Oracle can do all this for you automatically, seamlessly and with no locking contention problems using sequences and triggers. Here's how.

1) Create the table you want to generate keys for

create table mytable(col1 number, col2 etc ..., s_key number not null);

​ 2) Create a sequence used to populate the s_key column, this is your surrogate key.

create sequence myseq start with 1 increment by 1

Once created a sequence has two pseudocolumns which can be used, CURRVAL and NEXTVAL which are self-explanatory. To get at them use them in a  select as follows:-

SELECT myseq.nextval from dual;

3) Set up a database trigger on your table that ensures a sequence number is entered into it for each record inserted.

​ create or replace trigger mytrig
before insert on mytable
for each row
begin
    select myseq.nextval into :new.s_key from dual;
end;
​ / That's all there is to it.