Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    8

    Unanswered: Concepts - Should I design tables with Autonumbers or detect free ids myself ?

    Hi all!

    Are AutoNumbers considered good practice, in the sense of maintaining unique ids ? I have two tables which should contain identical primary keys (One-To-One relationship) and are joined together left-right (inner join) upon retrieval.

    However, I cant decide how most efficiently insert records into them ? As I insert record which shares its data in two tables, I am currently inserting row in the left table which has primary key as AutoNumber, and then finding out the id of that field, I insert data into second table with that ID as primary key, which in the case of the right table is just a number.

    Is this bad database practise ? I mean, how do I otherwise maintain integrity between inserting two one-to-one related rows into to tables ?

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Yes I AlWAYS LET MSACCESS have the Autonumber ID

    and building the relationship right access Will Put the Autonumber right

    I use this Format

    TableNameID -> AutoNumber = PrimaryKEY

    If I had a Table Called Customer

    there would be a CustomerID -> AutoNumber = PrimaryKEY

    then

    TranID
    ProductID
    CustomerID


    When I look at the Table A can See What ID is and the Primarykey is

    and whats link to what
    Last edited by myle; 07-11-04 at 20:58.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jul 2004
    Posts
    8
    Yes, but you cannot create Autonumbers on both tables, that would make it difficult to insert related data in both tables, since neither of them allow entering ID as number (it does it itself with Autonumber, but does not allow you) and the numbers inserted have to match in manner PK = FK, it wont happen , after all where is the guarantee that both Autonumbers for newly inserted rows will be the same ?

    Still pondering,
    Amn.

  4. #4
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404

    Smile Inserting Autonumbers

    You can insert an Autonumber in a table with an insert query, however I doubt if this is what you want.

    I did something similar a few year ago, but gave up on sharng the same ID on two tables, instead a "father" table was created and this initially created the entries in the four other tables (think it was four). The foreign keys from this to the "father" table were effectively their primary keys, although all of them had Autonumber primary keys.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Amn
    Yes, but you cannot create Autonumbers on both tables, that would make it difficult to insert related data in both tables, since neither of them allow entering ID as number (it does it itself with Autonumber, but does not allow you) and the numbers inserted have to match in manner PK = FK, it wont happen , after all where is the guarantee that both Autonumbers for newly inserted rows will be the same ?

    Still pondering,
    Amn.
    The table on the "right" would not use autonumber for this key. It would have it's own primary key autonumber.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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