msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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 on 20 May 08 | Tags:


Reader's Comments

  1. |

    You could argue that it should be impossible to bypass the api, by not granting privileges on the table to the users, only the execute privilege on the api. And to be really sure you can even revoke the create session privilege of the owner schema.

    This way, you can ensure that the sequence is used every time. Plus you get the added bonus of not having to use a database trigger. I’ve tested it as being 25-30% faster than using a database trigger.

    Just my two cents.

    Regards, Rob.

  2. |

    Isn’t this an approach of rather than educating developers just accepting that they will cause you problems and solving that by making sure they have no way to assign the value.

    I’m sure the developer would have wasted a few hours on figuring out why the keys used was +1 relative to the value he/she had provided. The only use of triggers I’ve found that is usable has been to work around software bugs while the software is bing fixed.

    Triggers is one feature I’ve never liked. It hides things, and further slows things down. In this case, what happens the day you need to delete and reinsert the row? The key will change. It may not be likely that you would need to do a delete+insert, but it could happen.

  3. |

    @Rob: Yes, I agree, it should be impossible to bypass the api.

    @Mathias: Yes, developers should be educated, but also rules, standards and security should be enforced. I have wasted time figuring out what the reason was behind the ORA-00001, where it could have been avoided by simply having a trigger or enforcing security as Rob suggested or both. I do not like triggers either as I stated in the post above.

  4. |

    “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.”

    Eddie, i agree with you here, perfectly!

    My “excuse” for allowing TRIGGERs is:

    I like TRIGGERs for DB maintenance. For example, if a history is maintained, a change-log, or the like, items that are in a sense part of the DB model, i believe the DB should do it. Hmm.. it isn’t so cut and dry, i guess i need to work on a more specific rule.

    Either way, in this case, the PK has no intrinsic value, and is just being used to have a good way to uniquely refer to the record.

    I have made a recent change in my policy, however, to allow overrides. In the TRIGGER i started to as WHEN (Old.Id IS NULL). This way, the TRIGGER is helpful, but doesn’t overwrite what the developer wants to do.

  5. |

    Of course, Brian’s innovation re-opens you to the problem you were trying to solve by writing the trigger. We have triggers like Brian’s, but we have a procedure to re-set the sequence to one higher than the maximum PK. We run it any time we insert rows with developer specified PKs – this is mostly when we do an import from data that already has PKs that we want preserved because we are also going to insert rows into a table with an FK that references the PK.

    As for triggers – I have a different opinion from Eddie – I LIKE to hide logic in triggers. That way developers can screw up their applications, but they CAN’T screw up the database. And it isn’t really hidden – SQL Developer or TOAD shows you what triggers are there. I comment the heck out of them so you know WHY they are there.

  6. |

    @Brian: You could have also added IF :NEW.ID IS NOT NULL in the trigger body, but in any case, this would have defeated the purpose of enforcing the use of a sequence.

    @John:

    but we have a procedure to re-set the sequence to one higher than the maximum PK That’s what I did to fix the problem. And it isn’t really hidden Right, if you know what tables have them and what events will trigger them.

  7. |

    @Eddie

    I understand your point. Personally, i don’t like to force the developers to do one way or another, unless it is a question of breaking data integrity.

    IMHO, developers make up their own numbers because it is not done for them. However, if a TRIGGER automagically pops the SEQUENCE for them, they won’t bother, unless they need to test a specific case.

    Also, there may be the situation where a TRIGGER is preffered, but now and then needs to be overrided because a query will not allow the use of a RETURNING clause. In which case, the SEQUENCE needs to be popped manually and the TRIGGER overridden.

    As for the IF, i thought WHEN is faster as it is evaulated before the TRIGGER is executed.

    @John

    I agree with Eddie on most TRIGGERs. It is a real pain trying to figure out who fired what and when.

    An then when people rely on the TRIGGERs, but some TRIGGERs have not been updated with the new rules, and so on, figuring out the logic (which may include firing order) is more trouble than its worth.

    If you want to control what developers can do to the DB, remove access to the TABLEs, and use PROCEDUREs instead to implement the approved methods. Using TRIGGERs is more of a way to cleanup, not prevent.