Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Auto increment

Re: Auto increment

From: Daniel S. Guntermann <>
Date: Mon, 12 May 2003 17:42:44 GMT
Message-ID: <o2Rva.68776$>

"Costin Cozianu" <> wrote in message news:b9oga1$li0j5$
> Daniel S. Guntermann wrote:
> > "Costin Cozianu" <> wrote in message
> > news:b9kodd$ka6pj$
> >
> >>Marshall Spight wrote:
> >>
> >>>"--CELKO--" <> wrote in message
> >
> An "auto-increment" style might suffice, i.e. an integer attribute that
> always gets assigned the next available value. Even in the later case
> Joe Celko's observation is absolutely pertinent: you better not make
> that column auto-increment, because techincally you're asking for
> trouble: you'll get in trouble with bulk inserts and import/export for
> example. You can easily solve the id generation issue without
> auto-increments,

Thanks Costin, I see your point.

Yet, I have to wonder. If a "relation" is defined correctly, even with a surrogate (or furthermore as an auto-increment system-generated) as the key, then in order to meet relational fidelity, the database need only be concerned with entity intregity (i.e. the key, and therefore row, is not duplicated). That is absolutely a specified part of the relational model in terms of structure and integrity. How the key or surrogate is generated is not within the scope of defining a relational system - at least to my limited knowledge. So what does it matter? The dbms can and should reject any attempts to add spurious auto-increment key generations (automatically for duplicates, and additional constraints can be user defined). Even in the situations you provide (bulk inserts and import/export), the dbms need only be concerned with enforcing entity intregity to the extent specified in its set of constraints. If the application has encoded dependencies on its knowledge of system-generated key values (e.g. incremental increases), then it should accept the risks of implementing such a decision.

An auto-increment feature might not be palatable to everyone or anyone for a wide variety of reasons, but it is, for the most part, an individual decision that can be influenced by requirements and environment. To me such a generator, whether auto-increment or random generation, seems can provide a useful and practical feature that can enhance productivity and provide some valid benefits - with all due respect. If how the key was generated was hidden from us by the DBMS, then it wouldn't matter what our opinions are, as long as the generation met the basic relational requirement that any value or set of values that compose a key are unique within a relation.


Dan Guntermann

> In any case, the idea is that by the time you ended the conceptual
> modeling (aka analysis, information modeling, etc), have all the
> business rules written down and came down to the nitty gritty details of
> database design per se, you better have identifying attributes for all
> your entities.
> Costin
Received on Mon May 12 2003 - 12:42:44 CDT

Original text of this message