Simulating IDENTITY Columns in Oracle

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.

Listing 2: Oracle

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.



Comments

David Keener By dkeener on Tuesday, December 11, 2012 at 09:09 PM EST

Yup, even after all this time, this solution is still useful. I ran into another situation today where I needed to do this...


Leave a Comment

Comments are moderated and will not appear on the site until reviewed.

(not displayed)