Thread

1. AUTO_INCREMENT suggestion

   D. Dante Lorenso <dlorenso@afai.com> — 1998-03-05T17:00:12Z

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

2. Re: [HACKERS] AUTO_INCREMENT suggestion

   Goran Thyni <goran@bs1.bildbasen.kiruna.se> — 1998-03-06T10:26:49Z

   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
   
   

3. Re: [HACKERS] AUTO_INCREMENT suggestion

   Mattias Kregert <matti@algonet.se> — 1998-03-06T10:45:46Z

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

4. Re: [HACKERS] AUTO_INCREMENT suggestion

   Marc G. Fournier <scrappy@hub.org> — 1998-03-06T13:31:17Z

   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"?
   
   
   
   

5. Re: [HACKERS] AUTO_INCREMENT suggestion

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-03-06T14:03:40Z

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

6. Re: [HACKERS] AUTO_INCREMENT suggestion

   ocie@paracel.com — 1998-03-06T20:24:26Z

   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
   
   

7. Re: [HACKERS] AUTO_INCREMENT suggestion

   Mattias Kregert <matti@algonet.se> — 1998-03-09T12:08:10Z

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