How do I make a multi-field key

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I make the primary key for a table to be the combination of two or
more fields. The fields are mostly foreign keys. I am using MS Access 2000.

Thanks for your help.
-Jeff
 
In table design view, you can click the "record selector" to the left of the
field name and drag down so you have multiple fields selected. Then click
the Key icon on the toolbar.

If the fields are not contiguous, you can use the Indexes dialog (View
menu.) Enter 2 rows of the dialog, with the IndexName blank on the 2nd row,
e.g.:
PrimaryKey Field1
Field2
Then select the first row of the index (the Field1 above), and in the lower
pane of the dialog set Primary to Yes.
 
How do I make the primary key for a table to be the combination of two
or more fields. The fields are mostly foreign keys. I am using MS
Access 2000.
Click to expand...

In table design view, ctrl-click the fields you want to make into the PK
and then click the yellow key button on the tool bar.

There is a long way round using the Indexes window, which is far from
intuitive but basically the only other way to do it.

You can also define the key programmatically using SQL, but that is very
rarely necessary.

Hope that helps


Tim F
 
One answer is don't even try. Make the primary key for that table an
autonumber field.

About now you are rightfully asking "Then how do I prevent duplicates?" Good
question. Sounds like the table might be a bridging or linking table to break
up a many-to-many relationship. In that case make a unique constraint or
index like so:

Open the table in design view.
Go to View then Indexes.
In the first blank row of the Indexes window, make up a name in the first
column.
Next select the first field name in the Field Name column.
After picking the first field name, go down and change Unique to Yes (if you
really want it as your primary key here's your opportunity)
Go straight down the column and pick the next field name.
Repeat as necessary.
You might want to change some of the sort orders, but it doesn't make that
much difference.
Now for the momemt of Truth! Save the table changes. Access will create the
new unique index. Access will also raise heck if you have any data that
violates the unique constraint and won't let is save.

You can check for problem data in advance with a Totals query like below:

SELECT Country.Contact,
Country.Country,
Count(Country.Country) AS CountOfCountry
FROM Country
GROUP BY Country.Contact,
Country.Country
HAVING Count(Country.Country) >1;

If the count of your selected fields is greater than one, that data needs to
be fixed before you can create a unique index (or PK).
 
How do I make the primary key for a table to be the combination of two or
more fields. The fields are mostly foreign keys. I am using MS Access 2000.
Click to expand...

Open the table in design view.
Ctrl-mouseclick each of the fields which constitute the key.
Click the Key icon on the toolbar.

John W. Vinson[MVP]
 
Back
Top