primary key - concatenated or autonumber?

Discussion in 'Access General' started by Susan H., Jun 20, 2013.

  1. Susan H.

    Susan H. Guest

    I have a parent (tblYouth) and child (tblAddress) tables with one-to-many relationship. I need help setting the correct primary key for tblAddress.

    tblYouth
    PK YouthID

    tblAddress
    PK? AddressID (autonumber)
    Conc PK? YouthID
    Conc PK? AddressBeginDate

    Sample Data:
    AddressID Youth ID AddressBeginDate AddressEndDate Address
    1 5 1/1/2013 3/4/2013 527 Main St.
    2 5 3/5/2013 Null 237 Elm St.
    3 6 1/1/2013 Null 422 Ash Road

    A youth will have many addresses, but the youth cannot have a duplicate AddressBeginDate. More than one youth may have the same AddressBeginDate. The YouthID + AddressBeginDate fields uniquely identify the record. The AddressID also uniquely identifies the record, but I'm wondering if I have to use the concatenated primary key because there can be multiple YouthIDs and multiple AddressBeginDates, but there can't be multiple YouthID + AddressBeginDate. Is using the concatenated fields as the primary key the only way to keep those combined fields unique?

    I'll be displaying and updating the latest address record for each youth ona subform, and I'll be using another subform to add records to the tblAddress. Comboboxes will be involved.

    Thanks!
     
    Susan H., Jun 20, 2013
    #1
    1. Advertisements

  2. Per Susan H.:
    I wouldn't even think twice: Autonumber PK or maybe, if circumstances
    warrant, manually-incremented PK.

    Every time I've tried using a PK with any intelligence built into it
    (SSN, whatever), I've lived to regret it.

    For my money, a PK should always be a blind, dumb number.
     
    (PeteCresswell), Jun 20, 2013
    #2
    1. Advertisements

  3. Susan H.

    Guest

    I'm a fan of simplicity, so I agree with you Pete. And thanks for the comment. So how do I keep users from a entering a duplicate AddressBeginDate forthe same youth? How do I keep the YouthID + AddressBeginDate field combinations unique?
     
    , Jun 20, 2013
    #3
  4. Susan H.

    Guest

    For others who may have this same issue, I found a primary key/index post that answers my questions. I'm using the AddressID autonumber as my primary key, and creating a compound index on the YouthID and AddressBeginDate fields so the two combined fields aren't duplicated.
     
    , Jun 20, 2013
    #4
  5. No reason to concatenate the fields. Like any other index, a Primary Key can
    have up to 9 separate fields in in.

    "Susan H." wrote in message

    I have a parent (tblYouth) and child (tblAddress) tables with one-to-many
    relationship. I need help setting the correct primary key for tblAddress.

    tblYouth
    PK YouthID

    tblAddress
    PK? AddressID (autonumber)
    Conc PK? YouthID
    Conc PK? AddressBeginDate

    Sample Data:
    AddressID Youth ID AddressBeginDate AddressEndDate Address
    1 5 1/1/2013 3/4/2013 527 Main St.
    2 5 3/5/2013 Null 237 Elm St.
    3 6 1/1/2013 Null 422 Ash Road

    A youth will have many addresses, but the youth cannot have a duplicate
    AddressBeginDate. More than one youth may have the same AddressBeginDate.
    The YouthID + AddressBeginDate fields uniquely identify the record. The
    AddressID also uniquely identifies the record, but I'm wondering if I have
    to use the concatenated primary key because there can be multiple YouthIDs
    and multiple AddressBeginDates, but there can't be multiple YouthID +
    AddressBeginDate. Is using the concatenated fields as the primary key the
    only way to keep those combined fields unique?

    I'll be displaying and updating the latest address record for each youth on
    a subform, and I'll be using another subform to add records to the
    tblAddress. Comboboxes will be involved.

    Thanks!
     
    Douglas J Steele, Jun 20, 2013
    #5
  6. Susan H.

    Phil Hunt Guest

    For primary key, use autonumber.

    On tblAddress, put a constraint (unique index) on YouthID+AddressBeginDate
    Please note that this index has nothing to do with primary key or foreign
    key. Just a constraint on the table


    I have a parent (tblYouth) and child (tblAddress) tables with one-to-many
    relationship. I need help setting the correct primary key for tblAddress.

    tblYouth
    PK YouthID

    tblAddress
    PK? AddressID (autonumber)
    Conc PK? YouthID
    Conc PK? AddressBeginDate

    Sample Data:
    AddressID Youth ID AddressBeginDate AddressEndDate Address
    1 5 1/1/2013 3/4/2013 527 Main St.
    2 5 3/5/2013 Null 237 Elm St.
    3 6 1/1/2013 Null 422 Ash Road

    A youth will have many addresses, but the youth cannot have a duplicate
    AddressBeginDate. More than one youth may have the same AddressBeginDate.
    The YouthID + AddressBeginDate fields uniquely identify the record. The
    AddressID also uniquely identifies the record, but I'm wondering if I have
    to use the concatenated primary key because there can be multiple YouthIDs
    and multiple AddressBeginDates, but there can't be multiple YouthID +
    AddressBeginDate. Is using the concatenated fields as the primary key the
    only way to keep those combined fields unique?

    I'll be displaying and updating the latest address record for each youth on
    a subform, and I'll be using another subform to add records to the
    tblAddress. Comboboxes will be involved.

    Thanks!
     
    Phil Hunt, Jun 20, 2013
    #6
  7. If you specify the YouthID and AddressBeginDate as an Index and specify it
    as Unique (setting it as Primary Key forces it to be a Unique Index), Access
    will not allow you to enter the same YouthID and AddressBeginDate. (If you
    think the error message is unfriendly, do your own error message).

    --
    Larry Linson
    Microsoft Office Access MVP
    Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

    in message
    I'm a fan of simplicity, so I agree with you Pete. And thanks for the
    comment.
     
    Access Developer, Jun 22, 2013
    #7
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.