Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: upsize access 2003 to sql server 2005

    All,
    I would like to upsize an access 2003 to sql server 2005. I havent done this in a while. Is it still good practice to use an autonumber id as primary key? I inherited this invoice database that consists of a table called Invoice with the primary key as the invoice number and a table called tracking with the tracking number as the primary key and the invoice number as the foreign key. All other tables are lookup tables used on the main form and all have id fields as autonumbers for their primary key. Should I leave it like this or add an id field and choose autonumber for the primary keys and use the invoice and tracking numbers as unique identifiers? Please advise.
    Thank you

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by slimjen View Post
    All,
    I would like to upsize an access 2003 to sql server 2005. I havent done this in a while. Is it still good practice to use an autonumber id as primary key? I inherited this invoice database that consists of a table called Invoice with the primary key as the invoice number and a table called tracking with the tracking number as the primary key and the invoice number as the foreign key.

    So far as I can tell, you have the right table structure.

    All other tables are lookup tables used on the main form and all have id fields as autonumbers for their primary key.

    The question is how are they related?

    Should I leave it like this or add an id field and choose autonumber for the primary keys and use the invoice and tracking numbers as unique identifiers? Please advise.
    Thank you
    I'm not sure how you would create the relationships by adding a unique identifier? Create an ER diagram and see how these tables are related then you will be closer to answering your own question.

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    Thank you for your response. The only relationship is from the invoice table; invoicenum to the tracking table invoicenum. The tracking table holds all the info. I am reluctant to change at this point. Giving the invoice table an autonumber could do more harm than good. I just didn't know if SQL still required that all tables have autonumbers.
    Thank you

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by slimjen View Post
    I just didn't know if SQL still required that all tables have autonumbers.
    SQL Server has never required this.
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2012
    Posts
    213
    If you are sure that the column invoicenum will always be unique, I believe you will not need to create another column for the primary key.

  6. #6
    Join Date
    Jul 2004
    Posts
    214
    Thanks for all your 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
  •