Muliple primary key

[Billy K]

Guys,

How do I set a table with multiple primary keys?

 

[Rick Brandt]

Guys,

How do I set a table with multiple primary keys?

A table can only have ONE PK. You might be thinking of a composite Primary Key
which is a single PK consisting of multiple fields. To do that hold the control
key while selecting all of the fields you want in the PK and then press the
"key" button in the tool bar.

If what you need is two different fields that don't allow duplicates, you can do
that using Unique indexes, but you can still only define one of them as the PK.

 

[Billy K]

I have never heard of the term composite primary key. What are some more
benfits?

I just came across an example database on the Internet which had multiple
primary keys. The table I am building has multiple fields regarding the one
item. I then have other tables that I would like linked back to this table.
This is why I was after multiple primary keys.

 

[Rick Brandt]

I have never heard of the term composite primary key. What are some more
benfits?

I just came across an example database on the Internet which had multiple
primary keys. The table I am building has multiple fields regarding the one
item. I then have other tables that I would like linked back to this table.
This is why I was after multiple primary keys.

The table you saw has ONE Primary Key that consists of multiple fields. There
is no such thing as a table with multiple PKs.

Many (likely most) developers these days use a surrogate field (like AutoNumber)
as their PK and therefore rarely use a composite PK. However; when using
natural keys for the PK you often don't have a single field that constitutes
uniqueness of the record. In those case you have to use as many fields as
required to guarantee that no two records will have duplicates in a combination
of those fields. That is a composite PK. For example in a table holding people
data you might use a combination of FirstName, LastName, and Birthdate as the
PK. While consisting of three fields, this is still a single PK.

 

[Lynn Trapp]

I have never heard of the term composite primary key. What are some more

benfits?

A composite primary key is a key made up of more than one field in the
table. Unless you use an artificial key (usually created using an AutoNumber
field), then you will most likely have to create a composite primary key.

I just came across an example database on the Internet which had multiple
primary keys.

I have some serious doubts that you really did. As Rick said, a table CANNOT
have multiple primary keys -- only composite keys

The table I am building has multiple fields regarding the one
item. I then have other tables that I would like linked back to this table.
This is why I was after multiple primary keys.

Are you saying that you want to link tables back to the primary table using
less than the full composite key? If so, you are in for a ton of trouble.
The foreign key in your child table should always consist of the full
primary key of the Parent Table.