Allan’s Musings

Just another WordPress.com weblog

Archive for the ‘SQL’ Category

Oracle Habits

Posted by Allan Peda on May 17, 2009

I am a big believer in following best practices, at least most of the time. In the case of Oracle databases that means creating surrogate keys. Even if they are of little use initially, I have found these unique identifiers are often useful later on. This is easy to do via auto numbering triggers.

Lets say we have a simple two column table, described below:

SQL> create table mytab (id integer primary key, descr varchar2(20));


The creation of an associated sequence and trigger will allow anyone to insert data into this table without bothering with the details of generating unique ID numbers.

SQL> create sequence mytab_seq;
Sequence created.
SQL> create or replace
trigger mytab_trg
before insert or update on mytab
for each row
BEGIN
  select nvl(:new.id,mytab_seq.nextval) into :new.id from dual;
END;
2    3    4    5    6    7    8  /
Trigger created.
SQL> insert into mytab (id, descr) values (1,'one');
1 row created.
SQL> insert into mytab (id, descr) values (null,'two');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from mytab;
	ID DESCR
---------- --------------------
	 1 one
	 2 two


Notice that the nvl() function always increments the sequence. This is important, as it allows users to manually add ID values without inadvertently triggering subsequent collisions. The only caveat I would add is that I am not sure if this is specified behavior, but I have observed it empirically
.

Advertisements

Posted in Programming, SQL | Tagged: , | Leave a Comment »