Thread: AUTO_INCREMENT suggestion

AUTO_INCREMENT suggestion

From

"D. Dante Lorenso"

Date:

05 March 1998, 14:58:52

To aid those of us that don't want to use sequences, can we add a
feature to 6.4 that allows the use of an AUTO_INCREMENT statement
when defining tables?  MySQL does this, and I like it.  It resembles
the Autonumber feature in Access as well.

create table tblFirm (
    FirmID int PRIMARY KEY AUTO_INCREMENT,
    FirmTypeID int,
    FirmName varchar(64) NOT NULL,
    FirmAlpha char(20) NOT NULL UNIQUE,
    FirmURL varchar(64),
    FirmEmail varchar(64)
);

Just yet another suggestion.

Dante
.------------------------------------------.-----------------------.
|  _ dlorenso@afai.com - D. Dante Lorenso  | Network Administrator |
| | |    ___  _ _  ___  __ _  ___  ___     |                       |
| | |__ / o \| '_|/ o_\|  \ |\_ _\/ o \    | Accounting Firms      |
| |____|\___/|_|  \___/|_|\_|\___|\___/    | Associated, inc.      |
| http://www.afai.com/~dlorenso            | http://www.afai.com/  |
'------------------------------------------'-----------------------'

Re: [HACKERS] AUTO_INCREMENT suggestion

From

Goran Thyni

Date:

06 March 1998, 08:27:39

D. Dante Lorenso wrote:
>
> To aid those of us that don't want to use sequences, can we add a
> feature to 6.4 that allows the use of an AUTO_INCREMENT statement
> when defining tables?  MySQL does this, and I like it.  It resembles
> the Autonumber feature in Access as well.
>
> create table tblFirm (
>     FirmID int PRIMARY KEY AUTO_INCREMENT,
>     FirmTypeID int,
>     FirmName varchar(64) NOT NULL,
>     FirmAlpha char(20) NOT NULL UNIQUE,
>     FirmURL varchar(64),
>     FirmEmail varchar(64)
> );
>
> Just yet another suggestion.
>

Informix calls something like this SERIAL type, like:

create table tblFirm (
     FirmID SERIAL PRIMARY KEY,
     FirmTypeID int,
     FirmName varchar(64) NOT NULL,
     FirmAlpha char(20) NOT NULL UNIQUE,
     FirmURL varchar(64),
     FirmEmail varchar(64)
);

Don't know if that is standrd or extension.

We use "CREATE SEQUENCE" to do this is PgSQL.

    regards,
--
---------------------------------------------
Göran Thyni, sysadm, JMS Bildbasen, Kiruna

Re: [HACKERS] AUTO_INCREMENT suggestion

From

Mattias Kregert

Date:

06 March 1998, 08:44:48

D. Dante Lorenso wrote:
>
> To aid those of us that don't want to use sequences, can we add a
> feature to 6.4 that allows the use of an AUTO_INCREMENT statement
> when defining tables?  MySQL does this, and I like it.  It resembles
> the Autonumber feature in Access as well.
>
> create table tblFirm (
>     FirmID int PRIMARY KEY AUTO_INCREMENT,
>     FirmTypeID int,
>     FirmName varchar(64) NOT NULL,
>     FirmAlpha char(20) NOT NULL UNIQUE,
>     FirmURL varchar(64),
>     FirmEmail varchar(64)
> );
>
> Just yet another suggestion.
>
> Dante

Since the PRIMARY KEY is implemented by creating an unique index
on the field, it should be easy to implement AUTO_INCREMENT by
automagically creating a sequence and setting it as the default for
this field.

Was PRIMARY KEY implemented in the parser?

/* m */

Re: [HACKERS] AUTO_INCREMENT suggestion

From

The Hermit Hacker

Date:

06 March 1998, 11:31:18

On Fri, 6 Mar 1998, Goran Thyni wrote:

> D. Dante Lorenso wrote:
> >
> > To aid those of us that don't want to use sequences, can we add a
> > feature to 6.4 that allows the use of an AUTO_INCREMENT statement
> > when defining tables?  MySQL does this, and I like it.  It resembles
> > the Autonumber feature in Access as well.
> >
> > create table tblFirm (
> >     FirmID int PRIMARY KEY AUTO_INCREMENT,
> >     FirmTypeID int,
> >     FirmName varchar(64) NOT NULL,
> >     FirmAlpha char(20) NOT NULL UNIQUE,
> >     FirmURL varchar(64),
> >     FirmEmail varchar(64)
> > );
> >
> > Just yet another suggestion.
> >
>
> Informix calls something like this SERIAL type, like:
>
> create table tblFirm (
>      FirmID SERIAL PRIMARY KEY,
>      FirmTypeID int,
>      FirmName varchar(64) NOT NULL,
>      FirmAlpha char(20) NOT NULL UNIQUE,
>      FirmURL varchar(64),
>      FirmEmail varchar(64)
> );
>
> Don't know if that is standrd or extension.
>
> We use "CREATE SEQUENCE" to do this is PgSQL.

    Just like PRIMARY KEY pretty much masks a 'CREATE UNIQUE INDEX',
why not SERIAL/AUTO_INCREMENT masking a "CREATE SEQUENCE"?

Re: [HACKERS] AUTO_INCREMENT suggestion

From

"Thomas G. Lockhart"

Date:

06 March 1998, 12:02:47

> > To aid those of us that don't want to use sequences

?? What is our next feature?

  "To aid those who don't want to use Postgres..."

Sorry, couldn't resist ;-)

> , can we add a
> > feature to 6.4 that allows the use of an AUTO_INCREMENT statement
> > when defining tables?  MySQL does this, and I like it.  It resembles
> > the Autonumber feature in Access as well.
> >
> > create table tblFirm (
> >     FirmID int PRIMARY KEY AUTO_INCREMENT,
> >     FirmTypeID int,
> >     FirmName varchar(64) NOT NULL,
> >     FirmAlpha char(20) NOT NULL UNIQUE,
> >     FirmURL varchar(64),
> >     FirmEmail varchar(64)
> > );
>
> Since the PRIMARY KEY is implemented by creating an unique index
> on the field, it should be easy to implement AUTO_INCREMENT by
> automagically creating a sequence and setting it as the default for
> this field.
>
> Was PRIMARY KEY implemented in the parser?

Yes, in gram.y and then is transformed into essentially a
CREATE UNIQUE INDEX statement afterwards, still in the parser-related
code. This kind of change is ugly, since it has side effects (an index is
created with a specific name which might conflict with an existing name),
but was done for SQL92 compatibility. I'd be less than excited about
doing ugly code with side effects (a sequence is created, etc) for
compatibility with a specific commercial database.

                                                     - Tom

Re: [HACKERS] AUTO_INCREMENT suggestion

From

ocie@paracel.com

Date:

06 March 1998, 18:24:35

Goran Thyni wrote:
>
> D. Dante Lorenso wrote:
> >
> > To aid those of us that don't want to use sequences, can we add a
> > feature to 6.4 that allows the use of an AUTO_INCREMENT statement
> > when defining tables?  MySQL does this, and I like it.  It resembles
> > the Autonumber feature in Access as well.
> >
> > create table tblFirm (
> >     FirmID int PRIMARY KEY AUTO_INCREMENT,
> >     FirmTypeID int,
> >     FirmName varchar(64) NOT NULL,
> >     FirmAlpha char(20) NOT NULL UNIQUE,
> >     FirmURL varchar(64),
> >     FirmEmail varchar(64)
> > );
> >
> > Just yet another suggestion.
> >
>
> Informix calls something like this SERIAL type, like:
>
> create table tblFirm (
>      FirmID SERIAL PRIMARY KEY,
>      FirmTypeID int,
>      FirmName varchar(64) NOT NULL,
>      FirmAlpha char(20) NOT NULL UNIQUE,
>      FirmURL varchar(64),
>      FirmEmail varchar(64)
> );
>
> Don't know if that is standrd or extension.

Sybase calls this an identity.  I don't think there is a standard name
for this, sigh.

Ocie

Re: [HACKERS] AUTO_INCREMENT suggestion

From

Mattias Kregert

Date:

09 March 1998, 10:06:55

ocie@paracel.com wrote:
> > > the Autonumber feature in Access as well.
> > >
> > > create table tblFirm (
> > >     FirmID int PRIMARY KEY AUTO_INCREMENT,
>
> > Informix calls something like this SERIAL type, like:
> >
> > create table tblFirm (
> >      FirmID SERIAL PRIMARY KEY,
> >      FirmTypeID int,
> >      FirmName varchar(64) NOT NULL,
> >      FirmAlpha char(20) NOT NULL UNIQUE,
> >      FirmURL varchar(64),
> >      FirmEmail varchar(64)
> > );
> >
> > Don't know if that is standrd or extension.
>
> Sybase calls this an identity.  I don't think there is a standard name
> for this, sigh.
>
> Ocie

How about adding all those keywords?
  AUTONUMBER, IDENTITY, AUTO_INCREMENT, SERIAL

Then, anybody could switch to PostgreSQL without having to relearn
this.

Would it be possible to have a "compatability" variable, like this?
  psql=> set sqlmode to {STRICT_ANSI|POSTGRESQL|ORACLE  ...}
so that ppl can set it to STRICT when they want to write portable
SQL, and PGSQL when they want all these nice features?

/* m */