Muliple primary key

  • Thread starter Thread starter Billy K
  • Start date Start date
Billy K said:
Guys,

How do I set a table with multiple primary keys?
Click to expand...

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.
 
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.
 
Billy K said:
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.
Click to expand...

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.
 
I have never heard of the term composite primary key. What are some more
benfits?
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
 
Back
Top