DISQUS

DISQUS Hello! Eddie Awad’s Blog is using DISQUS, a powerful comment system, to manage its comments. Learn more.

Community Page

Eddie Awad’s Blog

News, views, tips and tricks on Oracle and other fun stuff
Jump to original thread »
Author

The Lazy Developer’s Way to Populate a Surrogate Key

Started by Eddie Awad · 7 months ago

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 ... Continue reading »

7 comments

  • Rob van Wijk

    1 year ago

    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.
    reply
  • Mathias Magnusson

    1 year ago

    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.
    reply
  • Eddie Awad

    1 year ago

    @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.
    reply
  • Brian Tkatch

    1 year ago

    "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.
    reply
  • John Flack

    1 year ago

    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.
    reply
  • Eddie Awad

    1 year ago

    @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.
    reply
  • Brian Tkatch

    1 year ago

    @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.
    reply

Add New Comment

Returning? Login