Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2005
    Posts
    4

    Unanswered: Is is a good practice to use autoincrement in access

    Hi just new here in the forum i have a question

    is it a good practice to use autoincrement fields in an access database for master and detail? and for unique record identification. if not please tell why..
    im currently using access and with autoincrement for unique record identification and for master and detail.. then there is a possibilities to transfer from access to mssql. will i encounter problems when migrating since i am using an autoincrement fields

    in mssql is there an autoincrement fields like access?

    dont be harsh ok.. just anoob trying to learn.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The equivalent of autoincrement in SQL server is the IDENTITY property.
    It is possible to port existing ID values into a SQL Server table with an identity property set, but to do so you will need to temporarily turn off the identity property, and then reset it when the data load is complete.
    The upsizing wizard for Access -> SQL Server may handle this for you.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Dec 2005
    Posts
    4
    Quote Originally Posted by blindman
    The equivalent of autoincrement in SQL server is the IDENTITY property.
    It is possible to port existing ID values into a SQL Server table with an identity property set, but to do so you will need to temporarily turn off the identity property, and then reset it when the data load is complete.
    The upsizing wizard for Access -> SQL Server may handle this for you.
    thanks for the fast reply blindman.. but, is it a good practice to use autoincrement in a master detail ?? my master table will have an autoincrement fields, my detail will have a longint field to accomodate for the master's autoincrement field.
    i will be using this method on a library program ..

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What is going to be the primary key of the detail table? A natural composite key including the master ID, or are you going to create a new surrogate key (possibly autoincremented)?
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Dec 2005
    Posts
    4
    Quote Originally Posted by blindman
    What is going to be the primary key of the detail table? A natural composite key including the master ID, or are you going to create a new surrogate key (possibly autoincremented)?
    the primary key of the detail table will be another autoincrement fields but with no relation whatsoever on the master field.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    An surrogate key on a detail table is not required, though it may facilitate SQL coding and programming the interface. You can leave it off and still have functional database design using the natural key of the detail table. Note that the natural key of the detail table is usually a composite of the surrogate key of the master table and some detail element that is unique within each master record.
    One other thing; you keep asking whether it is good practice to use an autoincrementing column for a key. In SQL Server you basically have two choices for surrogate keys, and those are auto-incrementing identity values and GUIDs (Globally Unique Identifiers). Stick to using either of these. By definition surrogate keys should bear no relation to the underlying data, so you shouldn't be spending time mucking arround with methods of generating them. Hence, auto-generated surrogate keys such as Idenity columns and GUIDs are preferred.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Dec 2005
    Posts
    4

    Talking

    Quote Originally Posted by blindman
    An surrogate key on a detail table is not required, though it may facilitate SQL coding and programming the interface. You can leave it off and still have functional database design using the natural key of the detail table. Note that the natural key of the detail table is usually a composite of the surrogate key of the master table and some detail element that is unique within each master record.
    One other thing; you keep asking whether it is good practice to use an autoincrementing column for a key. In SQL Server you basically have two choices for surrogate keys, and those are auto-incrementing identity values and GUIDs (Globally Unique Identifiers). Stick to using either of these. By definition surrogate keys should bear no relation to the underlying data, so you shouldn't be spending time mucking arround with methods of generating them. Hence, auto-generated surrogate keys such as Idenity columns and GUIDs are preferred.
    thanks.. i thought that i am at a lost.. thanks for clarifying those things. at least now i can sleep well coz i wont change the autoincrement fields into my own generated nos.. thanks again blindman. your a great help..

Posting Permissions

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