为什么oracle没有主键的自动增量功能? [英] Why oracle does not have autoincrement feature for primary keys?

问题描述

有人能启发为什么oracle不支持主键的自动增量功能吗?

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

我知道在序列和触发器的帮助下可以实现相同的功能，但是为什么oracle没有引入autoincrement关键字来内部创建序列和触发器.我敢打赌，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?

推荐答案

它可能只是术语. "AUTOINCREMENT"表示将在记录"102"和"104"之间创建记录"103".在集群环境中，序列不一定是这种情况.一个节点可以插入"100"，"101"，"102"，而另一个节点正在插入"110"，"111"，"112"，因此记录是乱序的". [当然，术语序列"具有相同的含义.]

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.

然后，如果有人要在表中插入具有该字段特定值的行(即允许这样做，还是像DEFAULT一样工作)，或者有人尝试更新它，就会出现问题.如果有人插入"101"，则自动将跳转"设置为"102"，还是冒着尝试输入重复值的风险.

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.

它可能会影响其IMP实用程序以及直接路径写入和向后兼容性.

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.

编辑以添加:

在Oracle 12.1中，添加了对IDENTITY列的支持.

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

将为每个后续的INSERT语句从序列生成器中为Identity列分配一个递增或递减的整数值.您可以使用identity_options子句配置序列生成器."

"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

这篇关于为什么oracle没有主键的自动增量功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！