databasedev.co.uk - database solutions and downloads for microsoft access

What is a Key field in a Database and How should I choose one?

Keys are crucial to a table structure for many reasons, some of which are identified below:

• They ensure that each record in a table is precisely identified.
• They help establish and enforce various types of integrity.
• They serve to establish table relationships.

Now let's see how you should choose your key(s). First, let's make up a little table to look at:

PersonID	LastName	FirstName	D.O.B
1	Smith	Robert	01/01/1970
2	Jones	Robert	01/01/1970
4	Smith	Henry	01/01/1970
5	Jones	Henry	01/01/1970

A superkey is a column or set of columns that uniquely identify a record. This table has many superkeys:

• PersonID
• PersonID + LastName
• PersonID + FirstName
• PersonID + DOB
• PersonID + LastName + FirstName
• PersonID + LastName + DOB
• PersonID + FirstName + DOB
• PersonID + LastName + FirstName + DOB
• LastName + FirstName + DOB

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:

• PersonID
• LastName + FirstName + DOB

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.