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

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 25 Apr 2007 21:01:46 -0700
Message-ID: <1177560105.750049@bubbleator.drizzle.com>

Galen Boyer wrote:

> On Mon, 23 Apr 2007, damorgan_at_psoug.org wrote:

>> Galen Boyer wrote:
>>
>>>> If a developer can't handle
>>>>
>>>> INSERT INTO t
>>>> (idcol)
>>>> VALUES
>>>> (idseq.NEXTVAL);
>>>>
>>>> Then they should learn to say 'do you want fries with that'.
>>>>
>>>> I hate to be a bit harsh here but it isn't exactly like asking
>>>> them to step up to a real hardship like curly braces or assembler.
>>> Daniel, You are just being completely disingenuous. As the codebase
>>> grows and grows, it would be quite easy for even a very experienced
>>> developer to call the wrong sequence and it could take awhile before
>>> it might get uncovered. The less you have to manually tie things
>>> with code, the better. Thats just plain common-sense.
>> Not disingenuous ... humorous (I hoped).
>>
>> Of course it possible to make mistakes. It is possible to update the
>> wrong column. It is possible to assign a value to the wrong variable.
>> It is possible to log onto prod and truncate a table thinking it was
>> test (ask me how I know <g>).
>>
>> That is a good reason to have code reviews.
>> That is a good reason to have a test cycle.
>> That is not a good reason for autoincrementing columns.

> 
> But, this is why you are disingenuous.  You said you didn't like the
> SQLServer/Sybase autoincrementing column because of the way it is
> implemented in those engines, not the way it is used.
> 
> Or, are you saying that having a surrogate key is a problem.  If not,
> then what is the issue with autoincrementing?

I prefer natural keys to surrogate keys but they are not always available or the best solution to the problem so surrogate keys have their place.

The problem with autoincrementing, to repeat myself, is lack of flexibility. Consider the implications of autoincrementing on RAC and Data Guard or numerous other Oracle technologies not implemented in those other products. It could be a potential nightmare keeping things in synch.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Apr 25 2007 - 23:01:46 CDT