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/  |
'------------------------------------------'-----------------------'


Thu, 5 Mar 1998 12:00:12 -0500
"D. Dante Lorenso" <dlorenso@afai.com>
AUTO_INCREMENT suggestion
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

Fri, 06 Mar 1998 11:26:49 +0100
Goran Thyni <goran@bs1.bildbasen.kiruna.se>
Re: [HACKERS] AUTO_INCREMENT suggestion
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 */

Fri, 06 Mar 1998 11:45:46 +0100
Mattias Kregert <matti@algonet.se>
Re: [HACKERS] AUTO_INCREMENT suggestion
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"?



Fri, 6 Mar 1998 08:31:17 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] AUTO_INCREMENT suggestion
> > 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


Fri, 06 Mar 1998 14:03:40 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] AUTO_INCREMENT suggestion
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

Fri, 6 Mar 1998 12:24:26 -0800 (PST)
ocie@paracel.com
Re: [HACKERS] AUTO_INCREMENT suggestion
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 */

Mon, 09 Mar 1998 13:08:10 +0100
Mattias Kregert <matti@algonet.se>
Re: [HACKERS] AUTO_INCREMENT suggestion