Menu
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?
968
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. So you can indeed have an AUTO_INCREMENT column in a table that is not the primary key.
IDENTITY columns were introduced in Oracle 12c, allowing for simple auto increment functionality in modern versions of Oracle. Using the IDENTITY column is functionally similar to that of other database systems.
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
135
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
