Candidate primay keys??

[Jon]

Within Access 2007 is it possible to have some way to mark candidate keys?
Basically my boss asked me to use Access 2007 as his database of choice and I
have this table that retains information on car models. He also specified
that surrogate keys are to be avoided.

Table name: Model

Attributes:
Model_ID primary key A

OR

Make
Model
Variant

primary key B is a candidate pk imho and made up of the above 3 fields.

 

[Douglas J. Steele]

Mark them for what purpose?

There's no real reason why you have to had Model ID (which is presumably an
AutoNumber field): you can set the primary key to the combination of the
three fields you listed (you can have up to ten separate fields in an index,
which realistically is all a primary key is).

If you want to keep Model ID, you could always create an index on the three
fields and make it Unique.

 

[Lynn Trapp]

My first question would be like Doug Steele's -- Why do you want to "mark"
them other than to make them the primary key?

I wonder, though, if the VIN would not work as an excellent primary key. You
could then add a unique index on the combination of Make, Model, and Variant.

 

[Lynn Trapp]

That's quite true, Ken. Thanks for catching that.

 

[James A. Fortune]

Within Access 2007 is it possible to have some way to mark candidate keys?
Basically my boss asked me to use Access 2007 as his database of choice and I
have this table that retains information on car models. He also specified
that surrogate keys are to be avoided.

Table name: Model

Attributes:
Model_ID primary key A

OR

Make
Model
Variant

primary key B is a candidate pk imho and made up of the above 3 fields.

If your boss is insisting on a natural key then

http://en.wikipedia.org/wiki/Shikata_ga_nai

While queries that find duplicates can eliminate potential natural keys,
they don't guarantee that a given set of fields will work well. You
have to rely on common sense to come up with suitable candidate fields
for your key and then check to see if it will work with your present
data. You might discover later that you need to add another field,
perhaps a new one, to the set of fields you select.

Perhaps you still want to see all the current possibilities. Warning --
Read slowly: To find all combinations of fields comprising keys that
don't produce duplicates of the selected field values, converting a
For..Next counter into a binary number used to select fields for a
dynamically built SQL query that checks for duplicates inside the loop
might work provided the number of candidate fields is not too large.
I.e., only assign 1's or 0's to a subset of viable fields. Successful
combinations would be output to a table.

All that might have to be done in an Access 2007 Macro. I don't know
the answer to that because my users have been successfully running
earlier versions of Access from within Access 2007 so I have not yet had
to do any programming in Access 2007 directly.

James A. Fortune
(e-mail address removed)