oracle 为什么oracle没有主键的自增功能？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1204372/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why oracle does not have autoincrement feature for primary keys?
提问by Jugal Shah
Can someone enlighten on why is that oracle does not support an autoincrement feature for primary keys?
有人能解释为什么oracle不支持主键的自动增量功能吗？
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?
我知道在序列和触发器的帮助下可以实现相同的功能，但是为什么 oracle 没有引入 autoincrement 关键字来在内部创建序列和触发器。我敢打赌，oracle 的人肯定会考虑过这个问题。不提供这个功能肯定是有原因的。有什么想法吗？
采纳答案by Gary Myers
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.]
它可能只是术语。'AUTOINCREMENT' 意味着记录 '103' 将在记录 '102' 和 '104' 之间创建。在集群环境中，序列不一定是这种情况。一个节点可能会插入“100”、“101”、“102”，而另一个节点正在插入“110”、“111”、“112”，因此记录“乱序”。[当然，术语“序列”具有相同的含义。]
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.
然后，如果有人想将具有该字段的特定值的行插入到表中（即它是否允许，或者它是否像 DEFAULT 一样工作）或者如果有人试图更新它，那么你会怎么做。如果有人插入“101”，自动增量会“跳转”到“102”还是您冒着尝试重复值的风险。
It can have implications for their IMP utilities and direct path writes and backwards compatibility.
它可能会对它们的 IMP 实用程序和直接路径写入以及向后兼容性产生影响。
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.
在 Oracle 12.1 中，添加了对 IDENTITY 列的支持。
"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."
“将为每个后续 INSERT 语句从序列生成器中为标识列分配一个递增或递减的整数值。您可以使用 identity_options 子句来配置序列生成器。”
https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#CJAHJHJC
https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#CJAHJHJC
回答by Pavel Minaev
回答by ajh1138
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.
长期以来，这一直是各个 DB 阵营之间争论的焦点。对于像 Oracle 这样完善和构建良好的数据库系统，它仍然让我感到震惊，因为它需要如此多的代码和努力才能启用这个常用且有价值的功能。
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!
我建议只在您的工具包中放入某种增量主键构建器/函数/工具，并在 Oracle 工作中使用它。写信给你的国会议员，告诉他他们需要多么糟糕才能从 GUI 或使用单行 SQL 提供此功能！
回答by akf
Many have complained of this, but the answer generally is that you can create one easily enough with a sequence and a trigger.
许多人对此有所抱怨，但答案通常是您可以使用序列和触发器轻松创建一个。
回答by Kemal
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).
序列很容易失去同步（有人在数据库中手动插入记录而不更新序列）。Oracle 应该在很久以前就实现了！序列易于使用，但不像自动增量那么容易（它们需要额外的编码）。
