AUTO_INCREMENT MySQL-specific?

[redemption]

hey guys...

just a quick one: i was reading the PostgreSQL book by Bruce Momjian and i couldn't find the AUTO_INCREMENT attribute for defining columns... so i was wondering if this was specific to MySQL only?

and is the SERIAL type the ANSI SQL equivalent?

thanks

[MattR]

There is no ANSI standard for monotonically increasing integer columns. They're AUTO_INCREMENT in MySQL, SERIAL's in PostGRES, IDENTITY in Sybase/MS SQL, SEQUENCEs in Oracle, etc. etc.

When you really think about it they have nothing to do really with SQL nor relational algebra... They sort of violate the relational priniciples of 'you should always have ways to uniquely identify rows' since auto_increments (et al) are used primarily on tables which otherwise don't have a good PK.

[redemption]

thanks MattR ... i didn't know that

[djoni]

I use auto_increment column as surrogate key in all dimension tables of our dimensional data warehouse, though dimension tables have primary natural keys from their source operational system. Using surrogate key, instead of the natural key as primary key, is necessary in dimensional data warehouse to keep track of the change of the dimensional data, among its many purposes.

[swampBoogie]

There is no ANSI standard for monotonically increasing integer columns.

They were added in SQL 2003. Support for both generators and sequences is included.

[r937]

i wonder where SQL 2003 was when MattR made that comment in march of 2002!!

[swampBoogie]