Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Keys in tables (Access 2003 SP2)

    I need some opinions please.

    In defining tables I have been using the AutoNumber feature as the PK of the tables. What would you do if a lookup table had a 1 or 2 character code, would you still define an AutoNumber as it's PK as opposed to the 1 or 2 digit code?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Keys in tables (Access 2003 SP2)

    No, in such a situation an AutoNumber key only takes up extra space. I'd use the 1 or 2 character field itself as primary key, and set cascading updates for all relationships involving this key.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Keys in tables (Access 2003 SP2)

    Thanks Hans, that's what I have decided to do. It's different if it's a text field of some 30 characters, then that is a waste of space if the field is used as the primary key and is used as a foreign in other tables.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keys in tables (Access 2003 SP2)

    Hmmm.... to disagree with Hans, or not <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

    I prefer to always use an autonumber because they make it easier to write things like DLookup() functions or WHERE clauses of SQL statements -- no need to fuss with quotes. The consistency of knowing that the PK is always a number is valuable to me. It also depends on the size of the table -- if the table contains just a few records, then the extra space consumed by an autonumber is trivial. And if you can have a 1-2 character PK, then the table *must* be fairly small.

    But that's just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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