|
Many information technology professionals have to work with multiple types
of databases in their daily jobs. It's easy to get used to the convenience of a feature
supported by one database, only to discover that the feature isn't supported by
another database. The IDENTITY column provided by Microsoft SQL Server is a case in
point.
Listing 1 shows a table creation script for Microsoft SQL Server
that includes an IDENTITY column. Essentially, the BOOK_ID column, which is the primary
key for the table, is designated as an IDENTITY column. The important aspect of an IDENTITY
column is that the value of the column is automatically generated when a row is inserted.
Not only that, but the functionality of the IDENTITY column ensures that the generated
number will be unique.
Listing 1: Microsoft SQL Server
| CREATE TABLE book ( |
| book_id |
int IDENTITY(1,1), |
| title |
varchar(200) NOT NULL, |
| date_created |
datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| date_updated |
datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| description |
VARCHAR(2000) NULL, |
| PRIMARY KEY (page_id) |
| ); |
|
Based on the parameters provided for the IDENTITY, numbers will
be generated starting at 1 and incrementing by 1 each time a row is added. Needless
to say, this is pretty convenient. Too bad the Oracle database doesn't have a
similar construct.
Actually, though, it's pretty easy to simulate an IDENTITY column
in Oracle using a sequence and a trigger. A sequence is an Oracle database object
that generates unique numbers. As with an IDENTITY column, it can be set to start
at a certain number and increment by a desired number. Unlike an IDENTITY column,
though, a sequence isn't specifically tied to a particular table. Technically, this
means that a sequence can be used to generate unique IDs for multiple tables,
although this isn't a good practice.
A trigger is a block of code written in Oracle's PL/SQL language
that is designated to be executed under certain conditions, such as when a row is
inserted into a table. Within a trigger, the code has access to the values about to
be inserted into the table. When a trigger is executed, it is said to have "fired."
As shown in Listing 2, to simulate an IDENTITY column, an Oracle
trigger should be set to fire just before a row is inserted into a table. The trigger
checks whether the incoming primary key has a value or whether it's NULL. If the primary
key is NULL, then the trigger pulls the next value from the sequence and sets the
incoming value of the primary key column to that value.
| CREATE TABLE book ( |
| book_id |
INTEGER NOT NULL, |
| title |
VARCHAR2(200) NOT NULL, |
| date_created |
DATE DEFAULT sysdate NOT NULL, |
| date_updated |
DATE DEFAULT sysdate NOT NULL, |
| description |
VARCHAR2(2000) NULL, |
| CONSTRAINT XPK_book |
| PRIMARY KEY (book_id) |
| ); |
| |
| CREATE SEQUENCE book_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NOCYCLE; |
| |
| CREATE OR REPLACE TRIGGER book_bir |
| BEFORE INSERT ON book |
| FOR EACH ROW |
| BEGIN |
| IF :new.book_id IS null |
| THEN |
| SELECT book_seq.nextval INTO :new.book_id FROM DUAL; |
| END IF; |
| END; |
| / |
|
It's also important to point out a few useful conventions. First,
it's convenient to keep the table creation SQL statement, the sequence creation
statement and the trigger creation statement in one file so they don't get
separated over time (yes, just for fun, you try debugging code when somebody has rebuilt a
database and forgotten to create various triggers and sequences). Second, it's
also convenient to name the sequence and trigger after the table, so meaningful
error messages are produced by Oracle if there are problems with a table.
In the example above, the sequence has the same name as the table
but with "_seq" appended. The trigger also has the same name as the table, but with
"_bir" appended. This convention indicates that the trigger fires on a "before-insert-row"
basis. Check out this trigger naming conventions article
for more details about useful conventions for triggers.
|
