composite keys and foreign keys

[Fred]

In a response I received to a question, it mentioned that I should
create a unique index on the three or four column candidate key.

How do I do that?

It also mentions that three of my other ID columns constitute a
candidate key and can thus be the composite foreign key. I thought
keys were based on one column??

I am new and don't understand the candidate/ foreign terminology?

Fred

 

[Ken Sheridan]

Fred:

A candidate key is simply a column or set of columns whose value, or values
in combination, must be distinct in each row of the table. Often a table
will have several candidate keys from which a primary key can be selected.

To designate a set of columns as a composite primary key, in table design
view click on each while holding down the Ctrl key. This will select them
all. Then right click on the selection and select Primary Key from the
shortcut menu.

A foreign key, which again can be one or more columns, is not designated in
table design view. Rather it’s a result of creating a relationship between
tables. The column or columns in the referencing (many side) table on which
the tables are related is a foreign key.

Sometimes its less cumbersome, even where there is a composite candidate
key, to use an autonumber columns as a 'surrogate' primary key. If this is
done, however, any column or columns which constitute a candidate key must
also be indexed uniquely to protect the integrity of the data. An index on a
set of columns is created via the View menu in table design view. Select
Indexes and in the dialogue and select the relevant column names in its
second column, but put an index name (of your own choice) in only the first
row of the first column of the set. With this first row selected set the
index properties in the lower part of the dialogue.

Ken Sheridan
Stafford, England

 

[Klatuu]

Piggy backing on what Ken has to offer, I have found that the technique of
using an autonumber and a composite unique key is a good idea. It doesn't
seem to impact Jet performance much when you have a composite primary key;
however, I have seen some serios degradation in SQL Server - particularly if
the fields in the key are of different data types.
The most obvious places the degradation shows is in delete, append, and
update queries where the index structures have to be rebuilt.

As a habit, even when I am sure the application will never be upsized, I
always design so it can be with as little design impact as possible.

 

[Fred]

Fred:

A candidate key is simply a column or set of columns whose value, or values
in combination, must be distinct in each row of the table.  Often a table
will have several candidate keys from which a primary key can be selected..

To designate a set of columns as a composite primary key, in table design
view click on each while holding down the Ctrl key.  This will select them
all.  Then right click on the selection and select Primary Key from the
shortcut menu.

A foreign key, which again can be one or more columns, is not designated in
table design view.  Rather it’s a result of creating a relationship between
tables.  The column or columns in the referencing (many side) table on which
the tables are related is a foreign key.

Sometimes its less cumbersome, even where there is a composite candidate
key, to use an autonumber columns as a 'surrogate' primary key.  If this is
done, however, any column or columns which constitute a candidate key must
also be indexed uniquely to protect the integrity of the data.  An index on a
set of columns is created via the View menu in table design view.  Select
Indexes and in the dialogue and select the relevant column names in its
second column, but put an index name (of your own choice) in only the first
row of the first column of the set.  With this first row selected set the
index properties in the lower part of the dialogue.

Ken Sheridan
Stafford, England

Thanks for the information.
Fred