for Microsoft Access
Create complex MS Access databases without being an expert in relational database design! Designer for Microsoft Access asks you plain-language questions about what you want to manage with your database, and creates the tables and relationships automatically. Free trial available
Keys are crucial to a table structure for many reasons, some of which are identified below:
Now let's see how you should choose your key(s). First, let's make up a little table to look at:
A superkey is a column or set of columns that uniquely identify a record. This table has many superkeys:
All of these will uniquely identify each record, so each one is a superkey. Of those keys, a key which is comprised of more than one column is a composite key; a key of only one column is a simple key.
A candidate key is a superkey that has no unique subset; it contains no columns that are not necessary to make it unique. This table has 2 candidate keys:
Not all candidate keys make good primary keys: Note that these may work for our current data set, but would likely be bad choices for future data. It is quite possible for two people to share a full name and date of birth.
We select a primary key from the candidate keys. This primary key will uniquely identify each record. It may or may not provide information about the record it identifies. It must not be Null-able, that is if it exists in a record it can not have the value Null. It must be unique. It can not be changed. Any candidate keys we do not select become alternate keys.
We will select (PersonID) as the primary key. This makes (LastName + FirstName + DOB) an alternate key.
Now, if this field PersonID is meaningful, that is it is used for any other purpose than making the record unique, it is a natural key or intelligent key. In this case PersonID is probably not an AutoNumber field, but is rather a "customer number" for use, much like the UPC or ISBN.
However, if this field is not meaningful, that is it is strictly for the database to internally identify a unique record, it is a surrogate key or blind key. In this case Person ID probably is an AutoNumber field, and it should not be used except internally by the database.
There is a long running debate over whether one should use natural or surrogate keys, and I'm not going to foolishly attempt to resolve it here. Whichever you use, stick with it. If you choose to generate an AutoNumber that is only used to identify a record, do not expose that number to the user. They will surely want to change it, and you can not change primary keys.
I can now use my chosen primary key in another table, to relate the two tables. It may or may not have the same name in that second table. In either case, with respect to the second table it is a foreign key, and if in that second table the foreign key field is not indexed it is a fast foreign key.
Many thanks to JasonM, mdbmakers.com Moderator, at www.MDBMAKERS.com for permission to use the above article.