Does every table need a primary key ?

[Rock]

Hi,

In Summary...

I have a Contact form which has some Combo box fields.

These Combo boxes are fed data from other small tables which have in
most cases, only 1 field. I will call these tables my secondary tables
as they will do nothing except display the data in this one field into
the main Contact form

When I work on the assumption I must have a Primary key for every table,
I try to make this one field a Primary Key field. Unfortunately it does
not allow me, as it will have a Null value. This would be true until I
enter something. So it seems I will not be able to make a Primary Key
with this one field.

Now, if I add another field which is nothing but a Primary Key
(AutoNumber etc), then it is *that* data which is displayed in the
Contact Form Combo box, *not* the data from the field I want.

The tables without a Primary Key work for me, but I don't want to fly in
the face of everything I think I understand, which is every table must
have a Primary Key.

Tried to make this shorter but it made less sense than maybe it does now.

Thanks

Rock

 

[Jeff Boyce]

Rock

As you've already found, you don't need to designate a primary key in order
to use a table. Is it a good idea? (YES!) Do you have to use Autonumbers?
(NO!!)

Any value you are assured is unique for the row can serve as a primary key.

Note -- since Null means "I don't know", a field with Nulls can't be a
primary key.

JOPO (just one person's opinion)

Jeff Boyce
<Access MVP>

 

[Rick Brandt]

Hi,

In Summary...

I have a Contact form which has some Combo box fields.

These Combo boxes are fed data from other small tables which have in
most cases, only 1 field. I will call these tables my secondary tables
as they will do nothing except display the data in this one field into
the main Contact form

When I work on the assumption I must have a Primary key for every
table, I try to make this one field a Primary Key field.
Unfortunately it does not allow me, as it will have a Null value.
This would be true until I enter something. So it seems I will not be
able to make a Primary Key with this one field.

Hold on there. It should be the primary key in the table supplying the
RowSource for the ComboBox (the secondary table), not in the table where you are
creating the record. The secondary table is pre-populated with your choices
right? Therefore there are no nulls and that one field can perfectly well be
made the Primary Key of that table.

Now, if I add another field which is nothing but a Primary Key
(AutoNumber etc), then it is *that* data which is displayed in the
Contact Form Combo box, *not* the data from the field I want.

That can be controlled with the ColumnCount, RowSource, and BoundColumn
properties of the ComboBox. While I agree that a lookup table with a single
value does not necessarily need an additional numeric field to serve as the
Primary Key, if you DO go with that setup then it is the numeric value that you
would store in the other record that you are creating. Normally the ComboBox
would be set up to store the number while displaying the text.

The tables without a Primary Key work for me, but I don't want to fly
in the face of everything I think I understand, which is every table
must have a Primary Key.

Post back with a clarification because this post really didn't make much sense.

 

[Ronald Roberts]

The short answer is no. But, if you stop and think of all of the
things that can happen to the data by the user, then you may want
to add a key.

Example, you have a 1 field table that is used in a combo box to enter
data into a master table. let's say it is a catagory of some type.
One of the catagories is "WXYZ". You have 10,000 master records with
this catagory and now the user wants to change the catagory from WXYZ
to "WXYZ-A". You now need to run an update query to change all 10,000
records. If you had a key in your catagory table, and the key was in
the master record and not the actual decription, you will only need to
change 1 record in the catagory table.

When you want to use the master table, use a query and join on the
catagory key and make the description the result that goes into the
query. If you always use a query for your record source for your
forms and reports, you will always have the description and not the
key to display

To use an key or not depends on many different factors.
1. How large and complex the application is.
2. How large the database is.
3. If it is 1 user or multi user.
4. The amount of time given to develop the application.

and so on...

Ron

ps: What I don't understand is how you can have a 1 field table
and some records have null in that 1 field.