Should I use multiple primary keys?

[Ssstacey]

I have the following table:
YieldID |ProductID | Year | Month| Yield

I want to make sure that it is not possible to insert the same date (so, year and month together) twice for each productID. But at the other hand, it should be possible to insert the same date but then for different productIDs. How can I make sure that Access does this? Can I use (multiple) primary keys to enforce this?
Thank you,
Stacey

 

[jeremie_ingram]

YOu cant have multiple primary keys per say, but you can have a concatinated key which is TWO fields sat as a single key. This would be the best way to ensure that you do not have the same product twice on the same day. You do the by selecting the two fields (highlight), then click on the primary key button.

 

[ScottGem]

I have the following table:
YieldID |ProductID | Year | Month| Yield

I want to make sure that it is not possible to insert the same date (so, year and month together) twice for each productID. But at the other hand, it should be possible to insert the same date but then for different productIDs. How can I make sure that Access does this? Can I use (multiple) primary keys to enforce this?
Thank you,
Stacey

A primary key as ONE and ONLY one purpose and that is to uniquely identify a record. The purpose of a primary key is NOT to prevent duplication of data.. If you want to prevent data being entered for the same product for the same period then what you want is a multi-field INDEX. Access Help will tell you how to set up one.

 

[jeremie_ingram]

The primary key MUST BE UNIQUE to each record, and can be used to easily identify the record. You can prevent duplication of data by using the primary key, or else there would be no such thing as junction tables (which is what I belive the initial question was refrencing).

 

[Pat Hartman]

It is possible to use a compound primary key to do double duty as the unique identifier and to enforce a business rule. Current theory suggests using only autonumbers as primary keys since they make joins (and combos if you need one) easier and creating unique indexes to enforce business rules.

In either case, Jet supports compound primary keys and indexes of up to 10 columns. Other relational databases support more. The technique to create a compound index is different from that used to create a primary key. You need to open the indexes dialog. On the first empty line, give the index a name, select the first column and specify whether or not the index is unique. To add subsequent columns, add additional rows but leave the index name field empty. Just select the column name. If you need to add a second index, go to the next empty line, enter a new index name, choose the column, etc. You can choose ascendending/descending for each field of the index so you can choose ascending by customer and descending by date for example.

 

[Bat17]

I vote for the Autonumber Primary Key plus compound Index

I have lost count on how many times I got bitten by creating a query and only joining the tables by one of the Fields in a compound Key Get some strange results that way!

Peter

 

[Ssstacey]

Thank you..I figured it out. Is there a way I can change the message that pops up when someone tries to enter duplicate values?
Thanx
Stacey

 

[ScottGem]

The primary key MUST BE UNIQUE to each record, and can be used to easily identify the record. You can prevent duplication of data by using the primary key, or else there would be no such thing as junction tables (which is what I belive the initial question was refrencing).

Jeremie,
I must strongly disagree with you. It is not necessary to use a primary key to prevent duplication. A multi-field index set to No Duplicates does the same job without the clumsiness of a composite PK. Nor does this issue have anything to do with junction tables. As a point of fact, using a single field PK makes junction tables easier to manage.

I suggest searching the web on the phrase Natural vs Surrogate keys. You should find plenty of discussion about this. I support the surrogate key side of the debate.

Also, while compound keys are supported, that doesn't mean one should use them. Access supports a lot of things that best practices methodology says not to use.