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

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

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

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

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

CREATE SEQUENCE is also what ORACLE does.

Michael
--
Dr. Michael Meskes, Projekt-Manager | topystem Systemhaus GmbH
meskes@topsystem.de | Europark A2, Adenauerstr. 20
meskes@debian.org | 52146 Wuerselen
Go SF49ers! Use Debian GNU/Linux! | Tel: (+49) 2405/4670-44

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