Primary Key Help

  • Thread starter Thread starter Jack
  • Start date Start date
J

Jack

To Anyone,
Can anyone explain what a primary key is and how to use
access with out one. If anyone knows they could always enter a link to a
tutorial on the net somewhere
 
Hi Jack,
Can anyone explain what a primary key is
Click to expand...
A primary key is an attribute (column), or a collection of attributes (columns), whose values
uniquely identify each tuple (row) in a relation (table). A primary key must be unique; it cannot
be null. A primary key should be meaningless, so that you will never need to change its value.
A primary key is used to form a relationship between one or more tables.
and how to use access without one
Click to expand...
Why would you want to do this? Although Access will allow one to create a table without
specifying a primary key, this is never a good practice. All tables should have a primary key.

Tom
_________________________________________


To Anyone,
Can anyone explain what a primary key is and how to use
access with out one. If anyone knows they could always enter a link to a
tutorial on the net somewhere
 
Hi Jack!

Tom gave avery good explanation of primary key. I was only thinking that
when I first heard about it it was greek and it took a while before I
understud what it´s for and why it was nessesary. Maby you arn´t as slow as
I´m but I´ll explain it as simple as I can any way (no offence Tom!?).

Every person has a social security number. This number is unique and you are
the only one having "your" unique social security number. This is your
"primary key".

When you registrate your self as an owner of a car you use your social
securety number to. But the car has the registration number (licens plate)
which is unique for the car. This is the car´s "primary key".

With help of this primary key´s we can find out which car´s your´s. Who´s
the owner of a specific car and so on.

How should this look like in acces?

I would create 3 tables:

tblPerson (keeps data about a person and primary key would be social
security number).
tblCars (keep data about the cars, registration number as a primary key)
tblCarOwnerShip (keeps data about ownership of cars, auto number as a
primary key).

This is the design óf the tables:

tblPerson............tblCarOwnerShip........tblCars

.............................CarOwnerShipID (Auto number)
SSNo (1)----(M) fkSSNo
Name..................fkRegNo (M)------(1) RegNo
LastName...........RegDate.......................CarModel
Address..............(etc.)............................(etc.)

With this table design you can own several cars (tblCarOwnerShip) but each
car model only exist ONCE in tblCars and you (name, address etc.) only exist
ONCE in tblPerson.

// Niklas
 
Niklas,

Your explanation of the concept is great. I just want to comment on one
thing... your example. You are not the only person who makes the
mistake of thinking that a SSN is unique and universal. In practice,
there are so many anomalies that it is not a recommended practice to use
a SSN as a primary key in a database.
 
Jack,

I agree with Tom, except for one point... A primary key is often not
"meaningless", for example InvoiceNumber, StateCode, the examples are
multitudinous. In fact, there are many database developers who argue
that it should never be meaningless, i.e. that your should always use
"natural keys".
 
Hi Steve,

My response to the argument that a "natural" key should be used as a primary key is that as soon
as you have a primary key that has meaning, the need will likely come up to change it's value
sometime in the future. I think you will agree that a well chosen primary key should NEVER need
to be changed, right?

Take, for example, the 5-character primary key that you will find in the Northwind Customer's
table. It represents the name of the company, so clearly it has meaning. What if the company
decides to change it's name? You'll either have a primary key that doesn't match the pattern of
all your other primary keys, or you'll need to change the PK value. Yes, you can use Cascade
Update to cascade this change to all related tables, but, in doing so, you are putting more
stress on the database. See the following advice from Tony Toews, in the section titled: "Why I
don't like cascade updates and deletes"
http://www.granite.ab.ca/access/bestpractices.htm

I also refer you to a paper written by Luke Chung, President of FMS, Inc. See the section titled:
"Use Meaningless Field for the Key Field":
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

If one has a meaningless PK, there should never be a need to change its value. Thus, you won't
run into the issue that Tony discusses. I prefer using an autonumber data type, which the user
never sees on any forms or reports. Another benefit of doing this is that I never need to check
for a duplicate key. Why make your job harder by insisting on "natural" keys?

Text-based primary and foreign keys tend to require more bytes, on average, than a meaningless
autonumber, a long integer, which requires 4 bytes. I believe they are also slower to process,
in operations involving searching, sorting, etc.

Tom
___________________________________________


Jack,

I agree with Tom, except for one point... A primary key is often not
"meaningless", for example InvoiceNumber, StateCode, the examples are
multitudinous. In fact, there are many database developers who argue
that it should never be meaningless, i.e. that your should always use
"natural keys".
 
Ok!?

I don´t know how you create SSN in US I just know how it works in sweden and
here it´s a unique value created of two pcs. of data:
- Date of Birth (DoB)
- A unique 4 didgit number on just that date where the first 2 is a auto
number (I think) the 3:d didgit is allways odd if it´s a male and even if
it´s a femail. And the last didgit is control didgit calculated of the odher
3 AND DoB. I have forgot HOW it´s calculated though! :-)

With this system it´s impossible for a person, born in sweden, to recive the
same SSN as anyone else, unless a person makes a mistake or anyone have made
misstake in any SW used for this purpuse.

Anyway, even if so, it´s good enough for the most of the task. So I´m fine
with it!

// Niklas
 
Tom,

I make extensive use of AutoNumber primary key fields. However, if
there is a single field in the existing natural data which is unique, I
use this as the primary key. For example, in one of my databases I have
a table of Schools. Each school has a 4-character code which is
allocated by the educational authorities to each school in the country,
and this is used by schools and associated bodies in many ways. Am I
then going to add a "meaningless" autonumber field to this table? Eh?
And then have two fields in the table, both uniquely identifying each
record? No. And I'd be willing to bet that Tony Toews or Luke Chung
wouldn't either. Same goes for one of my databases for a tour operator.
They have a product id field where they enter stuff like KUON0204C1.
This means something to them. It is the primary key of this table, and
the basis of relationships with over 20 other tables. It works fine.
Anyway, my intention was not to re-kindle a debate which has been done
to death many times (and if we did, you'd find I was closer to you in
practice than many others would be). I just wanted to add a little
balance and breadth to the information being provided to Jack in answer
to his question.
 
Niklas,

I live in New Zealand, and as far as I know, there is no such thing as
anything like a SSN here :-) I don't know either how the SSNs in the US
are allocated. But as I understand it, there are millions of people
living in the US who either do not have one, or who have "borrowed"
someone else's. This particularly applies to illegal immigrants and
such like. It is not a good database PK. Possibly it is different in
your country, and as long as your database will only ever include people
who are born there, it may work ok.
 
Hi Steve,
Am I then going to add a "meaningless" autonumber field to this table?
Eh? And then have two fields in the table, both uniquely identifying each
record? No.
Click to expand...

That's certainly your prerogative, but I would still add the autonumber field to this table.
Why? Consider the effects on database size, speed and network traffic in a multi-user database.
Your 4-character code will take 4 bytes of storage if Unicode compression is set, or 8 bytes of
storage without it. So, as long as you have Unicode compression set, there would not be any
penalty for size versus a long integer data type. But what about speed? The Access Help file
states the following:

"When a field's Unicode Compression property is set to Yes, any character whose first byte is 0
is compressed when it is stored and uncompressed when it is retrieved."

I'm willing to bet that uncompressing this data is not free, as far as CPU time spent to
accomplish this operation.

On the tour operator's product id field, a value of KUON0204C1 would require 10 bytes of storage
in the primary table, and 10 bytes of storage for every related record in any child tables, if
Unicode compression was enabled. Lets assume there are 5000 unique product id's in the main
table, and that each product id has, on average, 500 related records in a child table. If I'm
doing my math correctly, using this text-based primary / foreign key would require:

5000 (10 + (500)(10)) = 25,050,000 bytes = 24,463 KB = 23.9 MB. (with Unicode compression)
or
5000 (20 + (500)(20)) = 50,100,000 bytes = 48,926 KB = 47.8 MB. (without Unicode compression)

On the other hand, if you add a "meaningless" autonumber field, so that you now have two fields
in the table, both uniquely identifying each record, the storage requirement would be:

5000 ((4 + 10) + (500)(4)) = 10,070,000 bytes = 9, 834 KB = 9.6 MB.

The more records a database has, the more dramatic the difference becomes.


Tom
_______________________________________


Tom,

I make extensive use of AutoNumber primary key fields. However, if
there is a single field in the existing natural data which is unique, I
use this as the primary key. For example, in one of my databases I have
a table of Schools. Each school has a 4-character code which is
allocated by the educational authorities to each school in the country,
and this is used by schools and associated bodies in many ways. Am I
then going to add a "meaningless" autonumber field to this table? Eh?
And then have two fields in the table, both uniquely identifying each
record? No. And I'd be willing to bet that Tony Toews or Luke Chung
wouldn't either. Same goes for one of my databases for a tour operator.
They have a product id field where they enter stuff like KUON0204C1.
This means something to them. It is the primary key of this table, and
the basis of relationships with over 20 other tables. It works fine.
Anyway, my intention was not to re-kindle a debate which has been done
to death many times (and if we did, you'd find I was closer to you in
practice than many others would be). I just wanted to add a little
balance and breadth to the information being provided to Jack in answer
to his question.

--
Steve Schapel, Microsoft Access MVP

_______________________________________

Tom Wickerath wrote:

Hi Steve,

My response to the argument that a "natural" key should be used as a primary key is that as soon
as you have a primary key that has meaning, the need will likely come up to change it's value
sometime in the future. I think you will agree that a well chosen primary key should NEVER need
to be changed, right?

Take, for example, the 5-character primary key that you will find in the Northwind Customer's
table. It represents the name of the company, so clearly it has meaning. What if the company
decides to change it's name? You'll either have a primary key that doesn't match the pattern of
all your other primary keys, or you'll need to change the PK value. Yes, you can use Cascade
Update to cascade this change to all related tables, but, in doing so, you are putting more
stress on the database. See the following advice from Tony Toews, in the section titled: "Why I
don't like cascade updates and deletes"
http://www.granite.ab.ca/access/bestpractices.htm

I also refer you to a paper written by Luke Chung, President of FMS, Inc. See the section titled:
"Use Meaningless Field for the Key Field":
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

If one has a meaningless PK, there should never be a need to change its value. Thus, you won't
run into the issue that Tony discusses. I prefer using an autonumber data type, which the user
never sees on any forms or reports. Another benefit of doing this is that I never need to check
for a duplicate key. Why make your job harder by insisting on "natural" keys?

Text-based primary and foreign keys tend to require more bytes, on average, than a meaningless
autonumber, a long integer, which requires 4 bytes. I believe they are also slower to process,
in operations involving searching, sorting, etc.

Tom
 
Tom Wickerath said:
Hi Steve,


That's certainly your prerogative, but I would still add the autonumber
Click to expand...
field to this table.

Would you do so even for a table of States?

You would use
StateID - autonumber
StateCode - 2 char text
StateName - full name

The StateCode isn't likely to change.
 
Well, even if we, in Sweden, have a unique SSN I don´t use it as a primary
key. Mostly because it´s quit private and not everyone whant to share this
with an assoaiation (which I´m now working on a db for. Therfore it´s better
to use a auto number field.

Thank´s for sharing your experience!

// Niklas
 
Hi Joan,
Would you do so even for a table of States?
Click to expand...

Yes, except I typically just have:
StateID - autonumber (PK)
StateName - full name

However, when I do include the StateCode, I skip adding an autonumber:
StateCode - 2 char text (PK)
StateName - full name


Tom
___________________________________

Tom Wickerath said:
Hi Steve,


That's certainly your prerogative, but I would still add the autonumber
Click to expand...
field to this table.

Would you do so even for a table of States?

You would use
StateID - autonumber
StateCode - 2 char text
StateName - full name

The StateCode isn't likely to change.
 
Come to think of it, for my next project that involves storing City & State data, I am thinking
about structuring a table as follows:

pkLocation - autonumber (PK)
ZipCode - text (or perhaps named PostalCode)
Primary - boolean
City - text
StateCode - 2 char text

This would be similar to the way that FMS shows these values stored in their Total ZipCode demo
database in the tblZipCodes table:
http://www.fmsinc.com/products/zipcode/index.html

This structure allows someone to create a form, where the user simply enters a zip or postal
code, and the city & statecode are filled in automatically. I believe it would help to prevent
the entry of inconsistent data, such as a user entering the following into a form:

City = Seattle
Statecode = TX (Texas)
ZipCode = 10508 (Maryland)

So, I'll definitely retain the autonumber PK in this case.

Tom
_______________________________________


Hi Joan,
Would you do so even for a table of States?
Click to expand...

Yes, except I typically just have:
StateID - autonumber (PK)
StateName - full name

However, when I do include the StateCode, I skip adding an autonumber:
StateCode - 2 char text (PK)
StateName - full name


Tom
___________________________________

Tom Wickerath said:
Hi Steve,


That's certainly your prerogative, but I would still add the autonumber
Click to expand...
field to this table.

Would you do so even for a table of States?

You would use
StateID - autonumber
StateCode - 2 char text
StateName - full name

The StateCode isn't likely to change.
 
Joan,
So there _are_ cases when you use a natural key.
Click to expand...
Yes, but these are very few and far between. I wrote earlier "A primary key should be
meaningless, so that you will never need to change its value" and I stand by that statement. Of
course, with your StateCode example, one really should not ever need to change its value.

The specific example I gave was in answer to Steve's reply. He had used two text-based keys--a
four character and a ten character key as his examples. I answered his statement "Am I then
going to add a "meaningless" autonumber field to this table? Eh?" by explaining exactly why I
would add an autonumber PK in those cases. If you had bothered to apply the same test that I
did, where you calculate the number of bytes required to store the data using the two character
statecode, then obviously one can come to the conclusion, as you did, that there is no benefit to
adding an autonumber.

Please tell me where I wrote that one should never use a natural key. I don't believe I made
that statement.

Tom
______________________________________


So there _are_ cases when you use a natural key.
 
I clearly misinterpreted your earlier statements to mean that you _never_
used natural keys - sorry, no need to get defensive.

Like Steve, I most often use a surrogate autonumber primary key. But if
there is a unique number/code for a record, and the business rules state
that it cannot change, I don't see a problem using that natural key.

I don't believe there are any hard and fast rules around this. One chooses
the primary key that is most suitable.
 
field to this table.

Would you do so even for a table of States?

You would use
StateID - autonumber
StateCode - 2 char text
StateName - full name

The StateCode isn't likely to change.
Click to expand...

The Province Codes for the Canadian Nortwest Territories and for
Labrador have both changed within the past few years.

Nonetheless I'm with you - my State table uses the 2 character code as
the PK (saving a join whenever I need just the state code in an
address).
 
I would like to put my two cents in. I think that it is not a good ide
to run around creating meaningless pieces of data to keep track of dat
that already have unique identifiers, Such as states or zip codes
Driver's Licence, etc.

When you create a top level table (say a Vehicle table for a car lot
do you create a unique identifier to describe a vehicle or do you us
the VIN number (already unique). The created number really has n
meaning to anyone outside the car lot (and probably not in the car lo
either).

Also the statement that all tables should have primary keys is bunk.
It may be a rare case, but sometimes the Key just gets in the way an
serves no purpose. If you create a single table that has no uniqu
way of identifying itself then adding a Primary key just adds more dat
to the db
 
Joan Wild said:
field to this table.

Would you do so even for a table of States?

You would use
StateID - autonumber
StateCode - 2 char text
StateName - full name

The StateCode isn't likely to change.
Click to expand...
 
Back
Top