Why oracle does not have autoincrement feature for

Question

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 6 years ago.

Improve this question

Can someone enlighten on why is that oracle does not support an autoincrement feature for primary keys?

I know the same feature can be achieved with the help of sequence and triggers, but why oracle didn't introduce the autoincrement keyword which will internally create a sequence and a trigger. I bet guys in oracle would have definitely thought about this. There must be some reason for not giving this feature. Any thoughts?

Answer 1

Many have complained of this, but the answer generally is that you can create one easily enough with a sequence and a trigger.

Answer 2

This has been a bone of contention for quite some time between the various DB camps. For a database system as polished and well-built as Oracle, it still stuns me that it requires so much code and effort to enable this commonly-used and valuable feature.

I recommend just putting some kind of incremental-primary-key builder/function/tool in your toolkit and have it handy for Oracle work. And write your congressman and tell him how bad they need to make this feature available from the GUI or using a single line of SQL!

Answer 3

Because it has sequences, which can do everything autoincrement does, and then some.

Answer 4

Sequences can get out of sync easily (someone inserts a record manually in the database without updating the sequence). Oracle should have implemeted this ages ago! Sequences are easy to use but not as easy as autoincrement (they require extra bit of coding).

Answer 5

It may just be terminology. 'AUTOINCREMENT' implies that that record '103' will get created between records '102' and '104'. In clustered environments, that isn't necessarily the case for sequences. One node may insert '100','101','102' while the other node is inserting '110','111','112', so the records are 'out of order'. [Of course, the term 'sequence' has the same implication.]

If you choose not to follow the sequence model, then you introduce locking and serialization issues. Do you force an insert to wait for the commit/rollback of another insert before determining what the next value is, or do you accept that, if a transaction rolls back, you get gaps in the keys.

Then there's the issue about what you do if someone wants to insert a row into the table with a specific value for that field (ie is it allowed, or does it work like a DEFAULT) or if someone tries to update it. If someone inserts '101', does the autoincrement 'jump' to '102' or do you risk attempted duplicate values.

It can have implications for their IMP utilities and direct path writes and backwards compatibility.

I'm not saying it couldn't be done. But I suspect in the end someone has looked at it and decided that they can spend the development time better elsewhere.

---

Edit to add:

In Oracle 12.1, support for an IDENTITY column was added.

"The identity column will be assigned an increasing or decreasing integer value from a sequence generator for each subsequent INSERT statement. You can use the identity_options clause to configure the sequence generator."

https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#CJAHJHJC