Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2008
    Posts
    20

    auto_increment primary key

    we can use integer as a table unique primary key and we can let the database to assign an unique number to it via the auto_increment property. Saying we can have a custId integer field as the only primary key and let the MySQL database automatically increase one each time a new customer is inserted. But I am not clear about this concept.

    1. does that auto_increment property is defined in SQL or in DBMS or in individual database implementation?

    2. in the insert SQL statement we should ignore that field (custId) because database will automatically assign a value to it?

    3. if my data is huge, is there any chance the integer value gets overflow if the database keep increase it?

    4. When we should use the auto_increment feature and when we should not use it?

    5. After compare the output sql file, I noticed that some modeler software support such feature but others did not. what does this mean?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. the auto_increment feature is not part of standard SQL

    however, most database systems find it very useful, and have implemented something for it

    MySQL: auto_increment
    SQL Server: identity
    Oracle: sequence
    etc.

    2. yes

    3. yes, but if you use INTEGER then you will typically need to redesign your database long before you hit the limit

    and if you use BIGINT, i guarantee you will never have too many rows

    4. when you need a surrogate key -- i'm sorry if this is not a big explanation, so you should do some research on that term

    5. it might mean you need to choose your modelling software with care
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    1) Implementation of Identity columns varies between database platforms.

    2) Yes.

    3) Perhaps sometime shortly before the Earth is engulfed by the Sun.

    4) Sometimes you should use it some of the time. Other times you should use it either none of the time, or all of the time. Depends on what time it is.

    5) It means some software is better than others.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am amazed at how closely our answers agree, blindman

    thank $deity you did not answer "4. all the time" (but just hang on, pat will be along in a moment, he'll do it)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I confess. I copied your answers while looking over your shoulder.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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