A record key is any field or combination of fields used to identify individual records in a table. Depending on how a key is used it may be described either as a primary key or a foreign key.
A super key is any attribute (field) or set of attributes that is unique for each row (record) in a table.
A candidate key is a super key with the additional condition that no smaller set of attributes within the set can be a super key. A candidate key must be able to function as the primary key of a table. Every table must include at least one attribute or set of attributes that can serve as a primary key even if that set includes every attribute.
Determining whether a particular candidate key will stand the test of time and always ensure uniqueness requires careful planning and analysis of the data that may be stored in the table over time.
For example, in one documented medical records case the developer determined that a candidate key that included patient's first name, last name, gender, and birthday should be used as the patient table's primary key. Only after the database had been in use some time was it discovered that the clinic in question had at least two patients with exactly the same first and last names, gender and birthday. In another case, a client told the author that she received another child's birth information when she requested her son's birth certificate. Another boy was born on the same day her son but in a different city. In both of these cases the initial data analysis stopped short of recognizing potential problems with the selected candidate key. These problems do not indicate a deficiency in the use of a candidate key as a primary key. They serve to illustrate that a complete understanding of candidate keys is essential to the design of a relational database.
Some developers prefer to use artificial or surrogate keys as primary keys. See #Natural and Surrogate Keys. In fact whether or not to use a surrogate key as a primary key is a common debate among theorists. What can not be debated is that an artificial key is not a candidate key. The sole purpose of an artificial key is to identify a single row in a table.
Primary and Foreign Keys
In relational databases, relationships require the presence of a primary key in one table and a foreign key field in a second table. Access requires that the primary key and the foreign key have the same size and data type. Although Access does not require a primary key in every table, many experienced Access developers recommend that every table's should include a primary key even if the table is not expected to ever be related to another table.
When you designate a field (or combination of fields) as a table's primary key, all values stored in that field (or combination of fields) must be unique. To put it another way, you will only find one record with any particular key value in the primary key field(s). When you refer to a particular key value to look up a record in a table, you will find only one record (assuming, of course, that the value has been assigned to the primary key of a record.
A foreign key is an attribute or set of attributes in one table that matches a unique attribute or set of attributes in the same or another table. Foreign key fields can have the same values repeated in several records.
In general a foreign key will be made up of the same attribute(s) as the primary key in the matching table, but that is not a requirement. Any candidate or surrogate key can be used as a foreign key.
Natural and Surrogate Keys
Regardless of whether a particular key is being used as a primary or foreign key, the key is described as natural or surrogate depending on how the key is formed.
A natural key uses one or more fields from each record to identify the record. A natural key is selected from the candidate keys that exist in the table.
A surrogate key uses a single value that is not part of the data itself to form the primary key. Access tables, for example, can include an autonumber field type that can be used as a surrogate key. Surrogate keys are meaningless outside of the database. (Some developers describe them as being not for human consumption.) Since the main purpose of a surrogate key is to facilitate data management this is not an issue.
Regardless of whether you use a natural or surrogate key or a single or multiple field key, there are practical considerations you should keep in mind.
Natural keys consist of values that are part of the record. This means that control of the actual values involved is outside the scope of the database and in the hands of a third party. While some values may appear at first sight to be suitable candidates as keys, there may be legal or other restrictions preventing their use in the database. In Canada, for example, a person's Social Insurance Number may only be used in connection with certain legally defined taxation and social program settings unless the person involved gives specific permission for another use. If control of the form of the values is beyond the scope of the database, the person or organization that does have control may change the form of the value, which would require modifications to the design of the database. This could be a costly turn of events.
Because each record's primary key must be different from every other primary key in the table (unique), a combination of several fields may be required. For example, in a table storing people's names with, say, three fields, NameFirst, NameMiddle, and NameLast, you could use the NameFirst field as the table's primary key only if you expect the table to contain very few records. (As the size of a group increases, the chance that at least two people will have exactly the same first name increases very quicky.) Combining the NameFirst and NameMiddle fields to form the primary key will only be slighly more reliable. Even using a combination of all three fields cannot guarantee that only one record will only ever have one particular combination of NameFirst, NameMiddle, and NameLast values. There have been cases where the primary key consisted of a person's, first, middle, and last names, plus gender and date of birth and still two people showed up with exactly the same three names, gender, and birthdate. The consequences could be catastrophic in a medical records database for example.
Control of the form of surrogate keys, on the other hand, can be kept completely within the scope of the database. That solves the problem of arbitrary changes to the values being left in outside hands but the tradeoff is that the values used have absolutely no meaning outside the database. A popular choice for surrogate keys in Access is the autonumber. The database itself will generate the autonumber in the process of creating a new record. However, there may be gaps in the number sequence for various reasons. If you use the autonumber strictly for data management purposes, then such gaps are a non-issue.
Single Field Keys
Single field keys simplify establishing table relationships and creating table joins in queries but you may need additional indexes to ensure that only unique combinations of several field values will be stored.
Composite keys, on the other hand, can be used to enuse that only unique combinations of field values will be store but they a layer of complexity when your establish table relationships and create table joins in queries. When creating a join involving a composite key, the join must include all fields of the key.
There are three special considerations if you are building a web database using Access 2010 and Access Services. First, all relationships must use Surrogate Keys, particularly with AutoNumber datatypes. Second, relationships must be defined as lookups to other tables--SharePoint doesn't support relationships without lookup information. Third, composite keys are not supported. You can get similar functionality with data macros and the BeforeChange event. Here is a blog post about how to restrict uniqueness across a combination of fields with data macros. http://blogs.msdn.com/access/archive/2010/02/18/composite-keys-in-web-databases-through-data-macros.aspx
See also Indexes
|This page has been accessed 5,685 times. This page was last modified 05:02, 5 February 2012 by Jack Leach. Contributions by BananaRepublic and Glenn Lloyd Disclaimers|