Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Primary Keys

Discussion in 'Information Technology' started by LurfysMa, Jul 28, 2006.

Tags:
  1. LurfysMa

    LurfysMa
    Expand Collapse
    Guest

    Most of the reference books recommend autonum primary keys, but the
    Access help says that any unique keys will work.

    What are the tradeoffs?

    I have several tables that have unique fields. Can I use them as
    primary keys or should I define an autonum primary key?

    One table has information about the 50 states in the US. The table
    looks like this:

    State Capitol Date Admitted
    Alabama Montgomery December 14, 1819
    Alaska Juneau January 3, 1959
    Arizona Phoenix February 14, 1912

    Since the state names are unique, is there any reason not to make that
    field the primary key?

    Another table has to do with grade school multiplication tables. This
    is a simple table something like this:

    Factors Answer
    1x1 1
    2x1 2
    2x2 4
    3x1 3
    3x2 6
    3x3 9
    ...
    12x1 12
    12x2 24
    ...
    12x12 144

    Here again, the Factors field (a text field) is unique. Is there any
    reason not to use it as the primary key?

    In both cases, the tables are small, so adding another 4-byte field is
    no big deal, but I'd like to keep the tables as simple as possible
    unless there is some downside.

    Thanks

    --
    Running MS Office 2000 Pro on Win2000
     
  2. Loading...

    Similar Threads Forum Date
    Controversial Surat school puts primary kids to agni pariksha Hard Talk Apr 22, 2010
    Singh Teaches Primary Pupils About Sikh Culture Sikh Sikhi Sikhism Jan 31, 2009
    Sikh News Cold wave shuts Punjab primary schools for a week (New Kerala) Breaking News Jan 31, 2008
    A Group Of Scientists Placed 5 Monkeys In A Cage And In The Middle, A Ladder With Bananas Inspirational Stories Apr 6, 2015
    India Monkeys Protect Indian Government Officials Breaking News May 19, 2011

  3. Barry Gilbert

    Barry Gilbert
    Expand Collapse
    Guest

    Using a column that is guaranteed to be unique, as in your examples, will
    usually work. The one place you would consider using an autonumber is if you
    expected to have to change the other key value. I don't expect any states to
    change their names any time soon, so it's probably ok there. In your other
    example, is there any risk that you might change the values in the factors
    field? On the other hand, even if you did need to change something, a
    cascading update relationship would still accomodate this.

    Bottom line: if you have a candidate field that, by its nature, is
    guaranteed to be unique, use it.

    Barry


    "LurfysMa" wrote:

    > Most of the reference books recommend autonum primary keys, but the
    > Access help says that any unique keys will work.
    >
    > What are the tradeoffs?
    >
    > I have several tables that have unique fields. Can I use them as
    > primary keys or should I define an autonum primary key?
    >
    > One table has information about the 50 states in the US. The table
    > looks like this:
    >
    > State Capitol Date Admitted
    > Alabama Montgomery December 14, 1819
    > Alaska Juneau January 3, 1959
    > Arizona Phoenix February 14, 1912
    >
    > Since the state names are unique, is there any reason not to make that
    > field the primary key?
    >
    > Another table has to do with grade school multiplication tables. This
    > is a simple table something like this:
    >
    > Factors Answer
    > 1x1 1
    > 2x1 2
    > 2x2 4
    > 3x1 3
    > 3x2 6
    > 3x3 9
    > ...
    > 12x1 12
    > 12x2 24
    > ...
    > 12x12 144
    >
    > Here again, the Factors field (a text field) is unique. Is there any
    > reason not to use it as the primary key?
    >
    > In both cases, the tables are small, so adding another 4-byte field is
    > no big deal, but I'd like to keep the tables as simple as possible
    > unless there is some downside.
    >
    > Thanks
    >
    > --
    > Running MS Office 2000 Pro on Win2000
    >
     
  4. Amy Blankenship

    Amy Blankenship
    Expand Collapse
    Guest

    In my opinion, you're asking for trouble if you ever show the user the
    primary key or if you might ever want to edit that information. Since
    primary keys are normally the way you establish relationships, you don't
    want them to ever change once a record has been created. Even though you
    may think the key value won't change, typos have been known to happen.
    Also, number fields take up less space in the database and primary keys, as
    the source of the relationship, are typically repeated over and over in many
    tables.

    Therefore, I always use autonumbers. Other opinions vary.

    HTH;

    Amy

    "LurfysMa" <invalid@invalid.invalid> wrote in message
    news:1f8ab25mq18uhqv6ml1qo5lu4gj782i53d@4ax.com...
    > Most of the reference books recommend autonum primary keys, but the
    > Access help says that any unique keys will work.
    >
    > What are the tradeoffs?
    >
    > I have several tables that have unique fields. Can I use them as
    > primary keys or should I define an autonum primary key?
    >
    > One table has information about the 50 states in the US. The table
    > looks like this:
    >
    > State Capitol Date Admitted
    > Alabama Montgomery December 14, 1819
    > Alaska Juneau January 3, 1959
    > Arizona Phoenix February 14, 1912
    >
    > Since the state names are unique, is there any reason not to make that
    > field the primary key?
    >
    > Another table has to do with grade school multiplication tables. This
    > is a simple table something like this:
    >
    > Factors Answer
    > 1x1 1
    > 2x1 2
    > 2x2 4
    > 3x1 3
    > 3x2 6
    > 3x3 9
    > ...
    > 12x1 12
    > 12x2 24
    > ...
    > 12x12 144
    >
    > Here again, the Factors field (a text field) is unique. Is there any
    > reason not to use it as the primary key?
    >
    > In both cases, the tables are small, so adding another 4-byte field is
    > no big deal, but I'd like to keep the tables as simple as possible
    > unless there is some downside.
    >
    > Thanks
    >
    > --
    > Running MS Office 2000 Pro on Win2000
     
  5. LurfysMa

    LurfysMa
    Expand Collapse
    Guest

    On Wed, 12 Jul 2006 11:48:27 -0500, "Amy Blankenship"
    <Amy_nospam@magnoliamultimedia.com> wrote:

    >In my opinion, you're asking for trouble if you ever show the user the
    >primary key


    Why is merely showing the user the primary key a problem?

    --
    Running MS Office 2000 Pro on Win2000
     
  6. RoyVidar

    RoyVidar
    Expand Collapse
    Guest

    > Most of the reference books recommend autonum primary keys, but the
    > Access help says that any unique keys will work.
    >
    > What are the tradeoffs?
    >
    > I have several tables that have unique fields. Can I use them as
    > primary keys or should I define an autonum primary key?
    >
    > One table has information about the 50 states in the US. The table
    > looks like this:
    >
    > State Capitol Date Admitted
    > Alabama Montgomery December 14, 1819
    > Alaska Juneau January 3, 1959
    > Arizona Phoenix February 14, 1912
    >
    > Since the state names are unique, is there any reason not to make
    > that field the primary key?
    >
    > Another table has to do with grade school multiplication tables. This
    > is a simple table something like this:
    >
    > Factors Answer
    > 1x1 1
    > 2x1 2
    > 2x2 4
    > 3x1 3
    > 3x2 6
    > 3x3 9
    > ...
    > 12x1 12
    > 12x2 24
    > ...
    > 12x12 144
    >
    > Here again, the Factors field (a text field) is unique. Is there any
    > reason not to use it as the primary key?
    >
    > In both cases, the tables are small, so adding another 4-byte field
    > is no big deal, but I'd like to keep the tables as simple as possible
    > unless there is some downside.
    >
    > Thanks


    I'd recommend you to take the time to use your favourite search engine
    for the terms like "natural vs surrogate primary key". Such search
    will
    probably list some of the pros and cons, in addition to hours of fun
    ;-)

    Basically, some favours usage of surrogate keys (Autonumber), others
    favours natural keys, which represents "things" having a business
    meaning, and which can also be a combination of fields. Some (including
    me) will use both, based upon the requirements. For state, I'd probably
    use the two letter code.

    Just be aware - for some this isn't just a matter of preference, it's
    religion to a degree thats close to fanatism.

    Just be sure that if you decide upon surrogate key (Autonumber), then
    remember that this will not ensure the integrity of your data! It will
    only ensure that each record has a unique number. Say in a table where
    you have a unique field, but you decide to add an Autonumber field for
    primary key, you will need to also add a unique index on the "natural
    key" field in addition to the primary key index on the Autonumber
    field,
    else you'll risk dupes.

    --
    Roy-Vidar
     
  7. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Primary keys build an index and if you are concerned with database size then
    two letter abbreviation for the state would be a smaller index.

    I have another case for your in that I have to keep training certifications
    and occupational examination records on personnel. But they keep changing
    departments, names, employee code when migrating to different subcontractors,
    etc. I set up an alias table that will have all changes and you can see that
    the database records reflect that Jane Doe, now married to Bill Smith, had
    hearing exam last year. The data matches paper records. There is a new
    alias record for every change and the front/top displays the latest always
    with a subform showing the current and all previous aliases.

    "LurfysMa" wrote:

    > Most of the reference books recommend autonum primary keys, but the
    > Access help says that any unique keys will work.
    >
    > What are the tradeoffs?
    >
    > I have several tables that have unique fields. Can I use them as
    > primary keys or should I define an autonum primary key?
    >
    > One table has information about the 50 states in the US. The table
    > looks like this:
    >
    > State Capitol Date Admitted
    > Alabama Montgomery December 14, 1819
    > Alaska Juneau January 3, 1959
    > Arizona Phoenix February 14, 1912
    >
    > Since the state names are unique, is there any reason not to make that
    > field the primary key?
    >
    > Another table has to do with grade school multiplication tables. This
    > is a simple table something like this:
    >
    > Factors Answer
    > 1x1 1
    > 2x1 2
    > 2x2 4
    > 3x1 3
    > 3x2 6
    > 3x3 9
    > ...
    > 12x1 12
    > 12x2 24
    > ...
    > 12x12 144
    >
    > Here again, the Factors field (a text field) is unique. Is there any
    > reason not to use it as the primary key?
    >
    > In both cases, the tables are small, so adding another 4-byte field is
    > no big deal, but I'd like to keep the tables as simple as possible
    > unless there is some downside.
    >
    > Thanks
    >
    > --
    > Running MS Office 2000 Pro on Win2000
    >
     
  8. LurfysMa

    LurfysMa
    Expand Collapse
    Guest

    On Wed, 12 Jul 2006 10:13:02 -0700, KARL DEWEY
    <KARLDEWEY@discussions.microsoft.com> wrote:

    >Primary keys build an index and if you are concerned with database size then
    >two letter abbreviation for the state would be a smaller index.


    Since there are only 50 states, the savings would be negligible even
    if we annex Canada one day! ;-)

    I was more interested in usage and reliability tradeoffs.

    >I have another case for your in that I have to keep training certifications
    >and occupational examination records on personnel. But they keep changing
    >departments, names, employee code when migrating to different subcontractors,
    >etc. I set up an alias table that will have all changes and you can see that
    >the database records reflect that Jane Doe, now married to Bill Smith, had
    >hearing exam last year. The data matches paper records. There is a new
    >alias record for every change and the front/top displays the latest always
    >with a subform showing the current and all previous aliases.


    Sounds messy...

    --
    Running MS Office 2000 Pro on Win2000
     
  9. Amy Blankenship

    Amy Blankenship
    Expand Collapse
    Guest

    Because by user I mean people other than the developer who might be charged
    with maintaining your data. When you show something to that type of user,
    you lay it open to being changed. Here's a full discussion of the issue
    http://www.dbpd.com/vault/9805xtra.htm

    "LurfysMa" <invalid@invalid.invalid> wrote in message
    news:4naab29a2jtsda1ecc6k2umesi533v0v4j@4ax.com...
    > On Wed, 12 Jul 2006 11:48:27 -0500, "Amy Blankenship"
    > <Amy_nospam@magnoliamultimedia.com> wrote:
    >
    >>In my opinion, you're asking for trouble if you ever show the user the
    >>primary key

    >
    > Why is merely showing the user the primary key a problem?
    >
    > --
    > Running MS Office 2000 Pro on Win2000
     
  10. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    "LurfysMa" wrote

    > Since there are only 50 states, the
    > savings would be negligible even
    > if we annex Canada one day! ;-)


    Your mileage may vary, but I've never done a database using states where I
    did not, sooner or later, need the state abbreviation, as well as other
    information. And, just for the record, my much-used and
    much-copied-from-database-to-database "state" lookup table is actually a
    table of "US states and Canadian provinces" and, any day now, I may have a
    client who will need Mexican states and their abbreviations, too.

    In any case, since I need them anyway, I index on the state/province
    abbreviation which may give a very minute performance advantage -- it
    certainly will not be very significant.

    Larry Linson
    Microsoft Access MVP
     
  11. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Just to play devil's advocate, at least two of the official provincial
    abbreviations have changed in Canada in recent memory (Quebec used to be PQ,
    and now is QC, Newfoundland and Labrador used to be NF, and now is NL). We
    also got a 3rd territory a few years back, but an addition to the table
    isn't as bad as a change to the PK.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Larry Linson" <bouncer@localhost.not> wrote in message
    news:O2pg7zdpGHA.1548@TK2MSFTNGP04.phx.gbl...
    > "LurfysMa" wrote
    >
    > > Since there are only 50 states, the
    > > savings would be negligible even
    > > if we annex Canada one day! ;-)

    >
    > Your mileage may vary, but I've never done a database using states where I
    > did not, sooner or later, need the state abbreviation, as well as other
    > information. And, just for the record, my much-used and
    > much-copied-from-database-to-database "state" lookup table is actually a
    > table of "US states and Canadian provinces" and, any day now, I may have a
    > client who will need Mexican states and their abbreviations, too.
    >
    > In any case, since I need them anyway, I index on the state/province
    > abbreviation which may give a very minute performance advantage -- it
    > certainly will not be very significant.
    >
    > Larry Linson
    > Microsoft Access MVP
    >
    >
     
  12. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    States! Do you know that:

    the state of North Dakota tried to change its name to just Dakota a couple
    years ago?

    in the '70s there was a movement to split California into three states?

    West Virginia was part of Virginia until the Civil War?

    there's been attempts to make Puerto Rico a state?

    Just goes to show that some things considered rock solid could change in the
    future. That's why I like autonumbers for primary keys.
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "LurfysMa" wrote:

    > Most of the reference books recommend autonum primary keys, but the
    > Access help says that any unique keys will work.
    >
    > What are the tradeoffs?
    >
    > I have several tables that have unique fields. Can I use them as
    > primary keys or should I define an autonum primary key?
    >
    > One table has information about the 50 states in the US. The table
    > looks like this:
    >
    > State Capitol Date Admitted
    > Alabama Montgomery December 14, 1819
    > Alaska Juneau January 3, 1959
    > Arizona Phoenix February 14, 1912
    >
    > Since the state names are unique, is there any reason not to make that
    > field the primary key?
    >
    > Another table has to do with grade school multiplication tables. This
    > is a simple table something like this:
    >
    > Factors Answer
    > 1x1 1
    > 2x1 2
    > 2x2 4
    > 3x1 3
    > 3x2 6
    > 3x3 9
    > ...
    > 12x1 12
    > 12x2 24
    > ...
    > 12x12 144
    >
    > Here again, the Factors field (a text field) is unique. Is there any
    > reason not to use it as the primary key?
    >
    > In both cases, the tables are small, so adding another 4-byte field is
    > no big deal, but I'd like to keep the tables as simple as possible
    > unless there is some downside.
    >
    > Thanks
    >
    > --
    > Running MS Office 2000 Pro on Win2000
    >
     
  13. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    > State Capitol Date Admitted
    > Alabama Montgomery December 14, 1819
    > Alaska Juneau January 3, 1959
    > Arizona Phoenix February 14, 1912
    >
    > Since the state names are unique, is there any reason not to make that
    > field the primary key?


    Yes, there are several good reasons. First, you might find some spelling
    errors. You might come out with a French version. Or, someone wants the
    names to be spelled in German, or whatever tickles your fancy. If you use a
    autonumber, and then start using a description for the State in place of the
    name, then your database can continue to function without modification.

    As others mentioned, there is much philosophy and strong views on each side
    of the camp (natural keys vs autonumber keys).

    My view is that when you relate a table, I simply want the database to

    please give me a relation between those two tables I specify. At that
    point, I give NOT one hoot about what field is used, and in fact I don't
    even want to waste my brain power coming up with a field to create the
    relaton. I want a one to many relaton. What you do after that is your
    business!!

    Here is my rant on this subject. It also explains why you don't every want
    to expose the autonumber to the end user.

    Be forewarned...this is a old post..and is a rant..but, it gives you the
    idea of how much fervor can go into the subject...

    ----------------

    Why would you EVE"R care what id ms-access uses for the relation?

    Do you care what memory segment word gets loaded into? Do you
    care if it is memory segment 32, or 8192?

    Are you now to ask users with a prompt as to what memory locaton that your
    word document will load into? Who cares..that junk is for comptuers to deal
    with...not humans...


    Who cares about a number you, and your users will NEVER see?

    An autonumber is some mechanistic to generate a number. To you and me, all
    we care about is that we have a relation from customers to customers invoice
    table. Do we really care, or have to know what number is used?

    Really, when word loads into memory, we don't care about the number used for
    the memory location. Really, when ms-access has a relation between customers
    and the invoice file...again we don't give a hoot about what number is used.
    Me, or you never sees the segment number when word loads, and we as users
    will never see the autonumber either.

    These numbers are NOT for humans to see.....

    There is a ZILLION kinds of internal numbers that your computer uses all day
    to function. Why do you care what memory segment numbers the computer used
    to load ms-access, or ms-word? Why care?

    If your folks can see, or use those autonumbers...then that is your problem
    with autonumbers.

    The real wrong being done here is that users can see, or use the
    autonumbers. I mean, do you want ms-word to start showing you the memory
    segment numbers it uses to load a document into memory? It would be crazy to
    force users to deal with memory segment numbers when using word.

    With ms-access, YOU NOW are the software developer. So, just like those
    developers who creased word, they don't show users what memory location the
    documents load into. You as a developer has a responsibly to NOT LET USERS
    see the autonumber.

    If you need some number for your users, then you need to write your own
    custom code that generates those numbers for human consumption (say, things
    like invoice number etc.). You do NOT want to use the invoice number for
    relations etc (you still use a internal autonumber, and that way you don't
    even care if the invoice has a invoice number, or perhaps you wait a
    specified time until a invoice number is given. Either way, you can still
    have your relational database function...but behind the scenes it is using a
    autonumber).

    Your database should not crap out just because you don't have a order number
    handy. Who even cares if you enter a order number, or not? Why should your
    database stop function if you don't enter a order number? Even if you change
    the order number, again..why should your database not work? Maybe you need
    to delete the order number? (again, what on planet earth does deleing some
    arbitrary number like the order number HAVE ANYTHING to do with building a
    functional relation between two tables? How possibility does these two
    separate concepts have anything in common?).

    You users should NEVER EVER see a autonumber.

    You mistake here is to try and let humans see, or even refer to, or use the
    autonumber in any way. Autonumbers are NOT to be given meaning by
    humans...but ONLY to your software.

    Who cars if you have a order number, or not? What does the fact of having a
    order number have to do with your database to functionally correctly? If you
    want to require that a order number HAS to be entered, then make the order
    number a required field, but that simple stupid order number HAS NOTING to
    do with setting up a relation between two tables.

    Setup your relation between tables with internal numbers, and your database
    will JUST WORK REGARDLESS of what fields, and things you decide to store as
    data. Do not go and attached some number out of the blue like a stupid order
    number to build relations between your tables. Can you imagine if products
    like QuickBooks, or even products like ms-word exposed internal numbers used
    for relations and other internal numbers as to how the software will
    function? Software uses ZILLIONS AND ZILLIONS of internal numbers and
    pointers to function.

    Now that YOU ARE the software developer, it is up to you to hide these
    numbers. You can expose these internal numbers (like autonumbers), but that
    is just rude, and just services to torture your users. Hide all the internal
    number stuff....every other developer before you did this....

    Why expose users to the exhaust pipe of a car when all they want to do is
    drive? Software is a machine you build. Build it...make it work, and then
    give it to your users. Users do NOT need to know about the kinds of teeth
    used in the gears for the car...

    So, the two concepts of how relations works is that many of us just believe
    that setting up a relation between two tables is a conceptual idea, and HAS
    NOTING to do with the data that you need to store. Others would disagree on
    this concept...

    By the way, there are some STRONG augments for using natural keys. For
    example, if I adopt a natural key in my data, then can freely move it
    between TWO DIFFERENT systems that respect this approach. (of course, you
    have to have those two systems respect that approach!!).


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.attcanada.net/~kallal.msn
     
  14. Lyle Fairfield

    Lyle Fairfield
    Expand Collapse
    Guest

    LurfysMa wrote:
    > Most of the reference books recommend autonum primary keys, but the
    > Access help says that any unique keys will work.
    >
    > What are the tradeoffs?
    >
    > I have several tables that have unique fields. Can I use them as
    > primary keys or should I define an autonum primary key?
    >
    > One table has information about the 50 states in the US. The table
    > looks like this:
    >
    > State Capitol Date Admitted
    > Alabama Montgomery December 14, 1819
    > Alaska Juneau January 3, 1959
    > Arizona Phoenix February 14, 1912
    >
    > Since the state names are unique, is there any reason not to make that
    > field the primary key?
    >
    > Another table has to do with grade school multiplication tables. This
    > is a simple table something like this:
    >
    > Factors Answer
    > 1x1 1
    > 2x1 2
    > 2x2 4
    > 3x1 3
    > 3x2 6
    > 3x3 9
    > ...
    > 12x1 12
    > 12x2 24
    > ...
    > 12x12 144
    >
    > Here again, the Factors field (a text field) is unique. Is there any
    > reason not to use it as the primary key?
    >
    > In both cases, the tables are small, so adding another 4-byte field is
    > no big deal, but I'd like to keep the tables as simple as possible
    > unless there is some downside.
    >
    > Thanks
    >
    > --
    > Running MS Office 2000 Pro on Win2000


    You could use whatever you want. IMO most of the criticism around this
    topic involves autonumbers being used as the primary key, and in
    addition, an attempt is made to use these autonumbers as ordinals,
    perhaps sequential invoice numbers, rather than merely nominals,
    identifiers of the records.
    Many developers routinely create an autonumber ID in every table as
    they create it. This, IMO, simplifies relationships (they are always
    (ID, ID) where ID = ID), and ensures that a unique identifier exists
    for each record, without concern for any meaning, duplication or
    possible nullability of that identifier (Access forms often are not
    updateable unless such an identifier exists).
    But if one has the concepts and skill, other primary keys are fine. Of
    course, many may not have the skill, and those who do will often choose
    autonumbers to standardize their approach to this matter.
    I use autonumbers. There are sufficient things to be planned and
    decided about db design without including ... what will my primary keys
    look like.

    BTW, some think of Primary Key as something "special". A primary key is
    simply the first created non-nullable unique index. Designating an
    index as primary will move it to position one (or return an error). We
    could easily do away with this term; I worked with indexes ( a thousand
    times more powerful and useful than JET or SQL-Server indexes) for many
    many years in the X-Base world without ever hearing it and I find no
    particular value in its availability.
     
  15. LurfysMa

    LurfysMa
    Expand Collapse
    Guest

    On Wed, 12 Jul 2006 14:15:59 -0400, "Douglas J Steele"
    <NOSPAM_djsteele@NOSPAM_canada.com> wrote:

    >Just to play devil's advocate, at least two of the official provincial
    >abbreviations have changed in Canada in recent memory (Quebec used to be PQ,
    >and now is QC, Newfoundland and Labrador used to be NF, and now is NL). We
    >also got a 3rd territory a few years back, but an addition to the table
    >isn't as bad as a change to the PK.


    OK, OK. I'm sold. I'll use an autonum field as the primary key. I
    suppose the phonetic English movement could still gather steam and
    California would become Kaliforia or something. ;-)

    --
    Running MS Office 2000 Pro on Win2000
     
  16. rkc

    rkc
    Expand Collapse
    Guest

    LurfysMa wrote:
    > Most of the reference books recommend autonum primary keys, but the
    > Access help says that any unique keys will work.
    >
    > What are the tradeoffs?


    The tradeoff is that some people will think you're an
    idiot if you use them and some people will think you're
    an idiot if you don't.

    As long as you understand that adding an autonumber
    as a primary key has nothing to do with the normalization
    process I think they are just fine.
     
  17. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    RoyVidar <roy_vidarNOSPAM@yahoo.no> wrote:

    >Just be aware - for some this isn't just a matter of preference, it's
    >religion to a degree thats close to fanatism.


    <chuckle>

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Please respond only in the newsgroups so that others can
    read the entire thread of messages.
    Microsoft Access Links, Hints, Tips & Accounting Systems at
    http://www.granite.ab.ca/accsmstr.htm
     
  18. hbinc

    hbinc
    Expand Collapse
    Guest

    Hi LurfysMa

    Nice discussions.
    Every record that has an relation with other records in other tables,
    must have an unique identification, that used in all the relations. As
    long as the "meaning" of the record stays the same, this identification
    stays the same. Whether it is Autonumbering or Random or whatever is
    not important, as long as it is unique.

    Independant of the the identification is what you choose as Primary
    Key. This may be your unique identification, but in fact can be any
    combination of any fields, as long as they do not contain null-values.

    But if you use a non-Autonumbering Primary Key, be sure that you use
    your unique identification in your relations!

    HBInc.



    LurfysMa wrote:
    > Most of the reference books recommend autonum primary keys, but the
    > Access help says that any unique keys will work.
    >
    > What are the tradeoffs?
    >
    > I have several tables that have unique fields. Can I use them as
    > primary keys or should I define an autonum primary key?
    >
    > One table has information about the 50 states in the US. The table
    > looks like this:
    >
    > State Capitol Date Admitted
    > Alabama Montgomery December 14, 1819
    > Alaska Juneau January 3, 1959
    > Arizona Phoenix February 14, 1912
    >
    > Since the state names are unique, is there any reason not to make that
    > field the primary key?
    >
    > Another table has to do with grade school multiplication tables. This
    > is a simple table something like this:
    >
    > Factors Answer
    > 1x1 1
    > 2x1 2
    > 2x2 4
    > 3x1 3
    > 3x2 6
    > 3x3 9
    > ...
    > 12x1 12
    > 12x2 24
    > ...
    > 12x12 144
    >
    > Here again, the Factors field (a text field) is unique. Is there any
    > reason not to use it as the primary key?
    >
    > In both cases, the tables are small, so adding another 4-byte field is
    > no big deal, but I'd like to keep the tables as simple as possible
    > unless there is some downside.
    >
    > Thanks
    >
    > --
    > Running MS Office 2000 Pro on Win2000
     
  19. onedaywhen

    onedaywhen
    Expand Collapse
    Guest

    LurfysMa wrote:
    > Most of the reference books recommend autonum primary keys, but the
    > Access help says that any unique keys will work.
    >
    > What are the tradeoffs?


    That is a good question.

    He's the position, as I see it, in brief.

    Codd introduced the idea of a primary key. He later realised that all
    keys are valid and that he was previously thinking non-relationally
    when he assumed one key would need to be nominated as 'primary'.

    RM theory has since moved on from the concept of primary keys. It was
    too late for SQL, though: SQL vendors implemented primary keys,
    assuming the PK would be given special meaning, and the concept of PKs
    was retro-fitted to the SQL standards.

    You can replace all your PRIMARY KEY constraints with NOT NULL UNIQUE
    because they logically equivalent. This is what the Access help means
    as referred to by the OP. However, in terms of physical SQL
    implementation, PRIMARY KEY has been given special meaning. This is why
    you are (correctly) still urged to designate a PRIMARY KEY for all your
    tables.

    What few people tell you is *how* to choose the PK.

    What it comes down to is this: for Access/Jet, what does PRIMARY KEY
    give you that NOT NULL UNIQUE does not? What is the special meaning for
    the particular product, Access/Jet?

    The answer, for Access/Jet the PK determines the (non-maintained)
    clustered index, the physical ordering on disk.

    So the next question is: what makes the best clustered index? The
    answer to this is that a clustered index favours BETWEEN clauses and
    GROUP BY clauses in SQL DML (queries, etc). In other words, your choice
    of PK in SQL DDL (design) is driven by you SQL DML (queries). The
    paradox here is that you can't write SQL DML before you've written your
    SQL DDL, so you need to keep your PK's under review.

    If you've understood the above you should come to the conclusion that a
    sole autonumber column will never make a good PRIMARY KEY in
    Access/Jet, because a random/incrementing integer/GUID does not make a
    good clustered index. I'd suggest that anyone who uses their autonumber
    column in a BETWEEN or GROUP BY construct has got something wrong in
    design and/or queries. I'd further suggest that anyone who uses BETWEEN
    or GROUP BY constructs which do not include columns that comprise their
    PKs are likely to have made a poor choice of PK.

    Jamie.

    --
     
  20. Lyle Fairfield

    Lyle Fairfield
    Expand Collapse
    Guest

    onedaywhen wrote:
    > The answer, for Access/Jet the PK determines the (non-maintained)
    > clustered index, the physical ordering on disk.


    Can you verify this?
     
  21. Lyle Fairfield

    Lyle Fairfield
    Expand Collapse
    Guest

    onedaywhen wrote:

    > What it comes down to is this: for Access/Jet, what does PRIMARY KEY
    > give you that NOT NULL UNIQUE does not? What is the special meaning for
    > the particular product, Access/Jet?
    >
    > The answer, for Access/Jet the PK determines the (non-maintained)
    > clustered index, the physical ordering on disk.


    From

    http://msdn2.microsoft.com/en-us/library/wd9d69b1.aspx

    THE CLUSTERED PROPERTY IS IGNORED FOR DATABASES THAT USE THE MICROSOFT
    JET DATABASE ENGINE BECAUSE THE JET DATABASE ENGINE DOES NOT SUPPORT
    CLUSTERED INDEXES.
     

Share This Page