0

i'm new to apex/oracle db and just found out that you would either use a sequence + trigger (usually for versions < 12c) or an identity-column ( versions >=12c).

What is better practice and what are the differences between the two ?

Thanks :)

LLLen
  • 23
  • 5
  • One advantage to IDENTITY is that it logically ties the sequence to the table, and if you drop the table the IDENTITY sequence disappears too. But the cautions noted below are very valid. I personally prefer IDENTITY even with the caveats. – Mark Stewart Mar 21 '16 at 17:35

2 Answers2

3

One big difference is in dealing with parent-child insertions - here you first need to insert the parent, and then use the generated ID value from the parent table as a foreign key in the child table's inserts.

In those instances, with an identity column you either need to be able to use the RETURNING clause to get back the just-inserted ID (not supported in all middleware), or you do the insert of the parent record and then query to get the ID that was created so that you can use it as the FK value in the child table. If your table does not have a natural key to easily identify the just-inserted row - this may be problematic.

On the other hand, for those situations, if you do not use IDENTITY you instead first do a SELECT on the sequence to get the next incremental value, and then use that directly in your parent and child insert statements. This is a more portable solution, and is compatible with all Oracle versions if you may need to do an install to an earlier version of Oracle for a given client. In that case you don't have the trigger do the select from the sequence to set the value - you do it yourself.

Yes, it is an extra round-trip to the DB to get the sequence.nextval, but if your middleware doesn't support the RETURNING clause you're going to be doing that round trip to get the inserted ID anyway, and almost certainly using a more expensive query.

Also, if you have a bunch of PL/SQL library code that manipulates data using the very convenient %ROWTYPE conventions, and if your IDENTITY column is set to GENERATED ALWAYS, then you can start running into problems on inserts as noted here. Something to be aware of if thinking of switching to IDENTITY columns underneath an existing code base.

Michael Broughton
  • 4,045
  • 14
  • 12
  • 1
    A good answer, but: "do a backwards install"? I can't get clients to upgrade much less downgrade.... – kevinskio Mar 09 '16 at 19:41
  • 1
    Poor choice of words. The point I was trying to mean was that sure, I can use it on my 12C Dev box, but if one of my customers is still on 10.2 then I can't port an Identity column backwards. – Michael Broughton Mar 09 '16 at 19:43
  • 1
    Interesting answer. I've not used identity columns yet, but I'm all for avoiding triggers where possible. I've used the approach of generating sequences manually in PL/SQL before an insert and I really like this, but it's something that some Oracle developers have a resistance to; mostly because they don't practice DRY. I like the idea of taking back control from the database where possible and putting it into the application layer instead (where I think it belongs). – Drumbeg Mar 10 '16 at 16:19
  • I'm not a fan of uneccessary triggers, but I also think there is a big place for stored business-level APIs. The UI shouldn't need to be re-written if the back-end needs to be refactored to support new functionality, and there can be security in having the front end be agnostic on the db structures. If I can expose a pl/sql API to limit DB round trips by having transactions be at the business object level instead of the database object level, then that lends itself to efficiencies on both sides of the equation. – Michael Broughton Mar 10 '16 at 16:48
2

There is a third alternative to the two mentioned in the question (IDENTITY column and sequence + trigger): namely, create a sequence and set a default on the column, e.g.:

CREATE SEQUENCE my_sequence;

CREATE TABLE my_table
( my_column NUMBER DEFAULT my_sequence.nextval NOT NULL
, my_other_column DATE DEFAULT SYSDATE NOT NULL
);
David Faber
  • 12,277
  • 2
  • 29
  • 40