SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    AUTO_INCREMENT MySQL-specific?

    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

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by MattR; Mar 29, 2002 at 12:11.

  3. #3
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks MattR ... i didn't know that

  4. #4
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    auto_increment column as PK in dimension tables

    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.

  5. #5
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    There is no ANSI standard for monotonically increasing integer columns.
    They were added in SQL 2003. Support for both generators and sequences is included.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i wonder where SQL 2003 was when MattR made that comment in march of 2002!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Smile



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •