Re: Why Oracle don't have AUTO_INCREMENT as in MySQL

On Sun, 15 Apr 2007, sybrandb_at_hccnet.nl wrote:
> On 15 Apr 2007 01:12:05 -0700, "howa" <howachen_at_gmail.com> wrote:
>

>>On 4??15??, ?U??12??04??, "Fuzzy" <fuzzy.greybe..._at_gmail.com> wrote:
>>> On Apr 14, 7:27 am, "howa" <howac..._at_gmail.com> wrote:
>>>
>>> > Seems AUTO_INCREMENT is very handy and natural, anyone agree?
>>>
>>> Seems SEQUENCE is very handy and natural.  Why don't others
>>> implement SEQUENCE?
>>>
>>> /Hans
>>
>>I can't agree SEQUENCE is more handy than AUTO_INCREMENT as you need
>>to do the same thing by two statements.
>>
>>Also, in real world, use of AUTO_INCREMENT as primary key (e.g. id) is
>>in fact, quite intuitive.
>>

Using them in practice is not even close to just typing two statements or do your users actually log into SQLPlus and type "two statements". You either have to create a trigger that assigns the next PK using a sequence or, in every single insert you have to reference the sequence.nextval function call. Ease of implementation is not an argument for sequences, at all.

>
> SEQUENCE is quite handy, as it is allows for multiple, similar tables
> with ONE surrogate.

This is my # 1 reason sequences win this argument.

> Also, in real world, AUTO_INCREMENT is equally artificial as SEQUENCE.
>
> But if you want to stick to Mickeysoft products, and don't want to
> admit the Oracle solution is better, you are by all means invited to
> do so.

What Oracle should do is extend the sequence and provide an AUTO-INCREMENT feature using them. Until they do, the fact that in the definition of the table can be used to define how the PK gets its surrogate value is point blank, easier to implement because it is less coding, and this coming from a guy who hates the limitations of the AUTO-INCREMENT from Sybase and SQLServer.

-- 
Galen Boyer