The Lazy Developer’s Way to Populate a Surrogate Key

Consider this table:

CREATE TABLE t
  (
     id   NUMBER PRIMARY KEY,
     name VARCHAR2(100)
  );

You want the column id to be populated from this sequence:

CREATE SEQUENCE t_s;

Here are a couple of ways you can do this:

1- Use the sequence’s next value directly in the insert statement:

INSERT INTO t (id, name) VALUES (t_s.nextval,'Eddie');

2- Use a database trigger:

CREATE OR REPLACE TRIGGER t_trig
   BEFORE INSERT
   ON t
   FOR EACH ROW
BEGIN
   SELECT t_s.NEXTVAL
     INTO :NEW.ID
     FROM DUAL;
END;

You will then insert a new row like this:

INSERT INTO t (name) VALUES ('John');

And you do not have to worry about the id column, unless of course you need it in subsequent code. In this case, use the RETURNING clause:

DECLARE
   l_id t.id%TYPE;
BEGIN
   INSERT INTO t (NAME) VALUES ('John')
     RETURNING id
          INTO l_id;
   DBMS_OUTPUT.put_line ('id: ' || l_id);
END;

So, when populating a surrogate key, which is better: using the sequence.nextval directly in the insert statement or using a database trigger?

In general, I do not like database triggers, they hide your logic and make it difficult to debug. But, in this case, I believe that using a database trigger to populate a primary key with a sequence value – or any unique value for that matter – is the right thing to do. Why? Well, I was troubleshooting an ORA-00001: unique constraint violated on a table’s primary key. The PL/SQL API that inserts rows into this table populates the key from a sequence, using the sequence.nextval in the insert statement.

A developer decides to “manually” insert rows into this table, bypassing the API all together. So, he queries the last number from the table’s primary key, and then inserts rows with hardcoded numbers in the key. He adds 1 to the number each time he inserts a new row. He does not use the sequence.nextval. Guess what happens next!

The next time the API is called to create a new row, the sequence generates its next number, but oops! this is the same number that the developer has already used. Boom! ORA-00001: unique constraint violated.

If the primary key was populated from a trigger, bypassing the value given to it from the developer, this error would not have happened.

Filed in Oracle with 7 Comments | Tags: sequence

---