Database Reference
In-Depth Information
Sequence Values as Default Values in Tables
One of the most dearly missing features from the author's point of view was the possibility to define the next value of
a sequence as the default value for a table. Quite often a developer finds himself in the situation that it was necessary
to populate an artificial key with a sequence value. A classic example is the use of an Oracle replication technology.
Some of Oracle's replication technologies require you to add a unique key constraint where there was none before.
When running prior versions, the developer is faced with a number of problems. Obviously, it is not possible to
insert a null value into the table as the application's code currently would. Not many applications have a PL/SQL API
to populate values in tables. If that was the case the addition of the new column would not cause any issues because
you could simply modify the API. For the more realistic case where a multitude of interfaces exist to populate table
data-external applications, SQL*Loader, a user interface written in APEX or similar you cannot guarantee that the key
will always be populated. A common solution to this kind of problem is the use of a before insert trigger. This way you
can guarantee that there will always be a key value! A trigger however always means overhead. Consider this simple
example: a table that requires a new key to be added using an ID column.
SQL> > desc t1
Name Null? Type
------------------------------------ -------- -------------------------
VC VARCHAR2(100)
T TIMESTAMP(6)
SQL> alter table t1 add id number;
Table altered.
SQL> alter table t1 add constraint
2 pk_t1 primary key(id);
Table altered.
SQL> create sequence s_t1 cache 1000;
Sequence created.
SQL> create or replace trigger bit_t1 before insert on t1
2 for each row
3 begin
4 if :new.id is null then
5 :new.id := s_t1.nextval;
6 end if;
7 end;
8 /
Trigger created.
With the trigger in place the application is not broken and the addition of the primary key has been added
completely transparently. However, there is a downside: performance will take a hit. Especially if you perform this
operation for many tables! Inserting a mere 100,000 rows into the table with the trigger enabled takes 8 seconds on a
test system.
Search WWH ::
Custom Search