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?
Because it has sequences, which can do everything autoincrement does, and then some.
Many have complained of this, but the answer generally is that you can create one easily enough with a sequence and a trigger.
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!
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.