Primary keys

[Guest]

dear community

can anybody let me know where can I find examples of a table with more than
1 primary key declared
and
how and when can I use more than 1 primary key in the data table ?

thanks for helping.

 

[Lynn Trapp]

A table can have only 1 primary key, but it is possible to have a primary
key that is made up of more than one field. In the design view of your
table, you hold the control key down and select all the fields you need to
be part of your key and then click the key button.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

You can have only one primary key. You can have other indexes.
What are you trying to accomplish?

 

[John Vinson]

can anybody let me know where can I find examples of a table with more than
1 primary key declared

No, because such a table is impossible by definition. A table can have
one and only one primary key.

That key can, however, consist of one field, two fields, or even ten
fields.

John W. Vinson[MVP]

 

[Guest]

Hi,

If I understood correctly, you only need this when you need to assign a
primary key to a field that is not unique; in this case you combine e.g. the
First name and Last name to establish a Primary key.

Regards,
Daniel A

 

[Guest]

perhaps I wrongly put in words, what John Vinson[MVP] described was right - i
should ask, "Many fields in a table which are declared as Primary
Key".....thanks to also Klatuu, Lynn Trapp, Daniel for the explanation as
well.

I was curious because I did come across a Table with not only 1 field
declared as Primary Key but had 3 fields being declared as Primary keys. (I
am referring to Access Application) - ?

as an example:

If a school have 4 trainers teaching 5 types of courses and all these
trainers are teaching all the 5 courses but all teach at different class
rooms and some of trainers are teaching at the same time and some are not.

in the above scenario, how would I place the Primary Key on which fields ?

thanks for the discussion and assistance render

 

[Guest]

You can define a primary key as one or more fields as long as the combination
is (1) unique and (2) has no nulls in any of the fields. However IMHO a
multifield PK leads to madness.

Sounds like you want one table to do the job of many. That's not how
databases work. In your case you need multiple tables.

1. Trainers. All the trainer information goes in here like name, address,
phone number. Use an Autonumber as the primary key.

2. Courses. Information about the course. Again use an autonumber for the PK.

3. Classes. A course can be taught more than one time.

4. Rooms. Info about the room. Again use an autonumber for the PK.

Now this will lead to a bunch of Many-to-Many relationships so you will
probably need at least 2 and maybe 3 joining or bridging tables. For example
a Trainer can more than one Course and a Course can have more than one
Trainer. Therefore you need a bridging or linking table like Classes.

Is your head hurting yet? If not, think about adding Students to this mix!

If this doesn't make any sense, I highly suggest getting some relational
database training or reading "Database Design for Mere Mortals" by Hernandez
before proceding any further on this database.

 

[John Vinson]

perhaps I wrongly put in words, what John Vinson[MVP] described was right - i
should ask, "Many fields in a table which are declared as Primary
Key".....thanks to also Klatuu, Lynn Trapp, Daniel for the explanation as
well.

I was curious because I did come across a Table with not only 1 field
declared as Primary Key but had 3 fields being declared as Primary keys. (I
am referring to Access Application) - ?

Subtle difference: You do not have three fields, each of them being a
primary key in its own right. Instead, you have three fields which
TOGETHER constitute a single Primary Key. Any one of the fields can
have duplicates, but you cannot create a new record which has
identical values to an existing record in all three fields.

as an example:

If a school have 4 trainers teaching 5 types of courses and all these
trainers are teaching all the 5 courses but all teach at different class
rooms and some of trainers are teaching at the same time and some are not.

in the above scenario, how would I place the Primary Key on which fields ?

thanks for the discussion and assistance render

Jerry's suggestions are on target. I wouldn't go quite so far as to
say you should NEVER have a multifield primary key, but the
information you describe should be stored in several tables, not just
one.

If you had a table with fields CourseID, TrainerID, and ClassLocation
and wanted to prevent entering the same course/trainer/location twice,
you could ctrl-click these three fields in table design view and click
the Key icon to make them a joint, three-field primary key. But... you
probably don't WANT a table structured in this way! See Jerry's post
for an alternative structure.

John W. Vinson[MVP]