Database Reference
In-Depth Information
■
Note
an
IDENTITY
column must be of a numeric datatype.
But note that it did not really skip the sequence generation—the sequence was generated automatically by
the database. In earlier releases of Oracle (11
g
and before), it would be typical to use the following logic to have an
autogenerated surrogate primary key:
EODA@ORA12CR1> create table t
2 ( pk number primary key,
3 other_data varchar2(20)
4 )
5 /
Table created.
EODA@ORA12CR1> create sequence t_seq;
Sequence created.
EODA@ORA12CR1> create trigger t before insert on t
2 for each row
3 begin
4 :new.pk := t_seq.nextval;
5 end;
6 /
Trigger created.
■
in releases before oracle 11
g
, you will have to use
SELECT T_SEQ.NEXTVAL INTO :NEW.PK FROM DUAL;
in place of the assignment. Direct assignment of a sequence in pl/SQl was a new 11
g
feature.
Note
This will have the effect of automatically—and transparently—assigning a unique key to each row inserted.
A more performance-driven approach in Oracle Database 11
g
and before would be simply this:
Insert into t ( pk, .... ) values ( t_seq.NEXTVAL, .... );
That is, skip the overhead of the trigger altogether (this is
definitely
my preferred approach). You can achieve
the same effect in the other databases using their types. The
CREATE TABLE
syntax will be different but the net results
will be the same. Here, we've gone out of our way to use each database's feature to generate a
nonblocking
, highly
concurrent unique key, and have introduced no real changes to the application code—all of the logic is contained in
this case in the DDL.
Layered Programming
Once you understand that each database
will implement features in a different way,
another example of defensive
programming to allow for portability is to layer your access to the database when necessary. Let's say you are
programming using JDBC. If all you use is straight SQL
SELECT
s,
INSERT
s,
UPDATE
s, and
DELETE
s, you probably don't
need a layer of abstraction. You may very well be able to code the SQL directly in your application, as long as you limit
the constructs you use to those supported by each of the databases you intend to support—and that you have verified
work exactly the same (remember the
NULL= NULL
discussion!). This means you'll have poorly performing SQL,
