PartNumber can be used as primary key

[Kyle]

When setting up a table, can I use actual PartNumber as a primary key instead
of assign one as PartNumberID with data type as autonumber. I would like to
use actual PartNumber as a primary key. But.. which way is better?

Any suggestion or recommend. Thanks.

 

[Ken Snell \(MVP\)]

If the part numbers are unique identifiers for the parts, then you can use
the part number as the primary key.

As for which is better -- using the part number or a autonumber -- well,
that is a matter of great debate among the developers. I personally have
used both approaches, depending upon my needs.

 

[Rick Brandt]

When setting up a table, can I use actual PartNumber as a primary key
instead of assign one as PartNumberID with data type as autonumber.
I would like to use actual PartNumber as a primary key. But..
which way is better?

Any suggestion or recommend. Thanks.

As suggested, it depends. Here is scenario where it would make a difference.

Let's say you also have a database tracking the history of a part number (design
changes etc..). Now at some point one of those changes is that the part number
itself is changed. Should the history database track the entire history (across
both the new and old part number) or should a new history begin from scratch at
the point the number was changed?

If you want the former then you need to be using a surrogate primary key. If
you want the latter then using the part number is not a problem.

 

[John Spencer]

Is PartNumber always unique? If you use two (or more) manufacturers or
suppliers will the part numbers still be unique? Is part number stable
(that is, it won't change over time)?

If the answers are yes for YOUR database then using PartNumber as the
primary key is probably a good idea. It will save space, make your queries
simpler since you often will need only the PartNumber and therefore won't
need to add the Parts table to your queries.

 

[HaLevi]

Expanding the above question if I may,

Is it possible to copy the whole column of autonumber to another column and
then edit it?

(Meaning: I sell books. I don't want to have to manually assign thousands
of different numbers to give part numbers for each of my listings. So if I
use the automatically assigned number for the record as the part number, and
then possibly add a few letters before the given number if necessary that
will tell me what category of book the given part number is in - would that
work? - Hope I am clear...)

 

[HaLevi]

What are the rules I guess - of the auto assigned numbers in Access?
I have read that once you don't have it in place when you start a database
it is too late to add it after. So if I want it used in a database that
didn't have it turned on, can I just start a new database and then move that
info in? Also, can I edit Autonumber? If I can't then - maybe I wasn't
clear before - then I cannot add the letters in front of it. In which case,
I want to have Autonumber and then copy the auto assigned number for each new
book automatically into a copy of that in a different column which we'll call
Item Number.

Thank you

 

[John Spencer]

An autonumber in Access is automatically assigned when a record is created
and even if the record is not saved, the number is used up.
The number will be unique for the entire table.
It cannot be edited.

You can add an autonumber field to a table at any time. I do not know what
algorithm the engine uses to decide which record gets which number - I would
suspect that the numbers are assigned in record storage order, but that is
just a guess.

Autonumbers are NUMBERS and therefore don't contain (and can't) contain any
letters.

Autonumbers often make good primary keys since they are automatically
generated, unique, and stable. However, they aren't good things to show to
the users since they often have little direct relationship to the data that
is stored in the row. They are a convenience in establishing and
maintaining relationships between tables.

 

[Tom Lake]

Autonumbers often make good primary keys since they are automatically generated,

unique, and stable. However, they aren't good things to show to the users since
they often have little direct relationship to the data that is stored in the row.
They are a convenience in establishing and maintaining relationships between
tables.

I like my PKs to have some relationship to the data. Something
like a SSN, or a company-generated PO # or Invoice #.

Autonumber fields have nothing to do with our data and are more
difficult to work with than a field of our own choosing. Yes,
there's more chance of error when the user is expected to
enter the PK manually but in practice we see no big source of error
here. The PK actually makes sense to the user so the user is more
likely to enter it correctly.

Tom Lake

 

[HaLevi]

Dear Mr. Spencer,
How do you add autonumber to a table that already has data in it. When I try
I am denied for the very reason that there is data?

 

[Douglas J Steele]

What do you mean by adding an autonumber to a table? Are you talking about
adding a new field to the table and making that field an Autonumber field,
or do you mean assigning the Autonumber field a value?

There shouldn't be any problem adding a new Autonumber field to an existing
table.

You shouldn't be trying to assign values to Autonumber fields.

 

[HaLevi]

I have tried a number of times to add an auto number column to a table and
every time it says you cannot because there is already data in the table. If
you know a way to do it anyway , please tell me.

 

[Rick Brandt]

I have tried a number of times to add an auto number column to a
table and every time it says you cannot because there is already data
in the table. If you know a way to do it anyway , please tell me.

You would get that error if you tried to change an existing number field to an
AutoNumber, but adding a NEW AutoNumber field should be no problem.

 

[Larry Daugherty]

With your table in design view, insert a row at the top of the design
form. Give the field the name of your table followed by "ID":
MyTableID. For type choose Autonumber. With that row/field still
selected, click the Key icon on the toolbar above the window. That
makes MyTableID the Primary Key of this table. Save your table return
to table view. You will find a value in every row of the field
MyTableID.

As for the value of an autonumber datatype, its ONLY purpose in life
is to generate unique values to serve as a surrogate Primary Key. Any
other use of the autonumber datatype will eventually lead you into
trouble. It is not guaranteed to be sequential.

The content of that field should never be seen nor used by a human
being. If it is shown troubles will come.

An earlier responder obviously prefers to use natural keys. That's
fine people should do what they feel to be best. The problem is that
the arguments he put forward don't address the real issues.:

====================================================
"I like my PKs to have some relationship to the data. Something
like a SSN, or a company-generated PO # or Invoice #."

SSNs are poor primary keys. They can be changed. So can every other
single thing in a record including a person's name. This from a fellow
with the scars of battles fought, some won and some lost. I've been
promised on the life of first born children that a certen element of
data "will never change". HAH! They never let me down, the promised
unchangeable element always changed. Company-generated PO#s can be
altered after the fact due to human error. I solved the problem to my
satisfaction by always using autonumber Primary Keys. Never again did
I have to sweat the primary keys nor waste the time formerly spent on
resolving issues thereto.


"Autonumber fields have nothing to do with our data and are more
difficult to work with than a field of our own choosing. Yes,
there's more chance of error when the user is expected to
enter the PK manually but in practice we see no big source of error
here. The PK actually makes sense to the user so the user is more
likely to enter it correctly."

Absolutely! Right On! Autonumber fields have absolutely nothing to
do with anyone's data and that is their greatest value because they're
immune to all changes to do with the user's data. Further, if you
never show it to your users, no one will ever be after you to change
it. Google these groups for years past and you'll see lots of posts
seeking help in managing autonumber values. In some cases it's just
that the programmer wants things to appear all neat and tidy (that's
really worrying about neat and tidy in the wrong places, expend your
efforts on the application because that's what you get paid to do).
However, in many cases, some control freak boss has seen the "ID"
label on a control and insists that the programmer jump through hoops
and make the autonumber behave per the control freak's whim of the
day.

IMHO, if you are using Autonumber primary keys and you require a user
to enter that autonumber value **for any reason whatever**, your
application is screwed up! By their very definition, autonumbers are
generated by Access. When that autonumber is referenced in a Foreign
Key, Referential Integrity takes care of it for you.

======================================================

If anyone is boxed in a corner and causing her or his users to enter
autonumber values manually to make an application work, please post
back into these groups expressing your problem and asking for help
resolving it. That's what these newsgroups are for.

HTH

 

[HaLevi]

Thanks to all who answered, especially Larry - IT WORKED!

 

[John Vinson]

(Meaning: I sell books. I don't want to have to manually assign thousands
of different numbers to give part numbers for each of my listings. So if I
use the automatically assigned number for the record as the part number, and
then possibly add a few letters before the given number if necessary that
will tell me what category of book the given part number is in - would that
work? - Hope I am clear...)

Well... that is NOT a good idea.

A field should have one purpose ONLY. Storing both a part number and a
category in one field is a violation of this principle. Stoer the part
number in one field; and store the category in a different field.

John W. Vinson[MVP]

 

[Tom Lake]

Well... that is NOT a good idea.

A field should have one purpose ONLY. Storing both a part number and a
category in one field is a violation of this principle. Stoer the part
number in one field; and store the category in a different field.

He can then use both fields as an aggregate PK.

Tom Lake