473,836 Members | 1,542 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Primary Keys

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
Jul 12 '06 #1
115 6290
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" <in*****@invali d.invalidwrote in message
news:1f******** *************** *********@4ax.c om...
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

Jul 12 '06 #2
On Wed, 12 Jul 2006 11:48:27 -0500, "Amy Blankenship"
<Am********@mag noliamultimedia .comwrote:
>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
Jul 12 '06 #3
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
Jul 12 '06 #4
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" <in*****@invali d.invalidwrote in message
news:4n******** *************** *********@4ax.c om...
On Wed, 12 Jul 2006 11:48:27 -0500, "Amy Blankenship"
<Am********@mag noliamultimedia .comwrote:
>>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

Jul 12 '06 #5
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...th is 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...t hen 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
pl************* ****@msn.com
http://www.attcanada.net/~kallal.msn


Jul 12 '06 #6
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.

Jul 12 '06 #7
rkc
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.
Jul 12 '06 #8
RoyVidar <ro************ *@yahoo.nowrote :
>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
Jul 12 '06 #9
Per LurfysMa:
>What are the tradeoffs?
Two tradeoffs in favor of surrogate keys that I haven't heard mentioned yet:
------------------------------------------------------------------
1) What looked unique yesterday may turn out not to be today.
U.S. social security number being a case in point. You'd *think*
that there won't be any dupes, but over the long run there will be.
For instance in a charitable endowment environment, the same SSN may
identify both a person and a legal entity that manages money - and
if people and legal entities have the same properties in that app,
you'll run into problems using SSN as a PK. And then there is the
issue of people with bogus SSNs....

2) For me, at least - who has only a limited number of brain cells available -
it's convenient when coming back to maintain an app that I haven't looked
at in a year or more not to have to figure out what natural relationships
exist and how/why. All I have to do deal with is "...ID".
------------------------------------------------------------------
--
PeteCresswell
Jul 13 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
3592
by: John Simmons | last post by:
How is it that even though I have the column "username" in my database set as a Primary key, using my PHP script to add new users to the database works without any errors even when signing up using an existing username. I have a database full of the same usernames!
1
1728
by: Lucas Gump | last post by:
Is it possible to add primary keys to a table one-time and there are already multiple primary key defined? I have to change sp_id, so_id as primary keys to the existing primary keys dn_slot_id and dn_mm_id. I tried several times but all failed: 1. alter table debit_slot_detail add primary key (sp_id, so_id);
6
2509
by: Andreas | last post by:
Hello list, what about uniqueness of inherited primary keys ? eg you have : create table objects ( id int4, date_created timestamp(0), primary key (id)
7
2960
by: Ilan Sebba | last post by:
I am trying to add a record using SQL. My problem is that the primary keys are foreign keys, and these foreign keys are autonumbers. I therefore do not know the primary keys of the record I am trying to insert. I therefore do not think that I can use the sql "Insert Into" command. Here is a simplified illustration of my tables: tblFather NaturalKey1 NatuarlKey2
7
5357
by: Philip | last post by:
Hey all, (Access 2000) I've been having a horror story with this design problem. My Database is Structured like This: AUTHORS, BOOKS, PAGES. Those are the Tables and each Item in each table needs a unique ID# based on its context. Primary Keys AUTHORS = AuthorID - NO Duplicates
7
2475
by: Dave | last post by:
Hi, Maybe I'm missing something with the DataKeyField attribute of a datagrid but it seems that it's somewhat limiting since this only allows you to specify one field as the key. I have a table that has two keys and it seems you need this value to find the corresponding record in the DataSet in order to update the data from the Datagrid.
9
3917
by: sonal | last post by:
Hi all, I hv started with python just recently... and have been assigned to make an utility which would be used for data validations... In short we take up various comma separated data files for eg: area.txt, school.txt, students.txt.... and so on (ok?!?) now, 1. area code used in the school.txt must be defined in the area.txt (Primary key in area => area_code defined in area.txt & Foreign key on school => area_code defined in...
2
9168
by: Danny | last post by:
Hello, We imported a bunch of tables from a database and realized that the primary keys weren't copied to the destination db. In order to re- create the keys, we need to know which tables have them. Is there a command that I can use (on the source db) to find out which tables contain primary keys? The db has hundreds of tables and I'd rather not go through each one to see which has a primary key. Also, for future reference, is there a...
4
3838
by: Peter | last post by:
I am interested in informed feedback on the use of Constraints, Primary Keys and Unique. The following SQL statement creates a Bands tables for a database of bookings Bands into Venues, where the rule of the business is that only band plays on the one night. The SQL statement prevents a Band name being repeated (as it is Unique). Similar statement for the Venues. CREATE TABLE Bands (BandID varchar(5) CONSTRAINT BandID PRIMARY KEY, Band...
0
9825
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10846
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10551
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9379
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6979
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5828
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.