A key field is a column or combination of columns in a database that uniquely identifies the specific entry in question. These fields are used to identify and enforce integrity on various tables in a database. These fields come in two general types: simple and compound keys, depending on the number of columns marked as the key field. When used in your original table, this field is known as the “primary” key; when it is used on another table as a method of linking the tables, it is known as a “foreign” key.
Woman doing handstand with a computer
To be useful as a key field, a candidate key must be unique, unalterable, and required. Uniqueness is simple to understand; this means that no other entry in the database can have a matching key value like any other entry. Immutable means that the information in the key value is fixed and immutable, never changing over time. Required means that for no entries in the database, the key value can be left blank. For example, an optional category such as race or nationality cannot be used as a key field in an employee database because some employees may not be willing to provide this information.
Choosing the right key fields allows the database to maintain its integrity while remaining consistent across tables. For example, if a database lists employee records, a typical entry might contain the employee’s first name, last name, date of birth, and social security or identification number. The obvious choice for a key field is the social security number or employee identification number; both would meet the requirements of uniqueness, inalterability and mandatory nature. When trying to look up a specific employee record in the database, entering the number would not immediately return more than a single employee record. If you use the employee’s last name as the key field, multiple employee records may be found because more than one employee may share the last name.
One way to reduce the chances of accidentally choosing a key field that has the potential to return multiple records is to use a composite key. Unlike a simple key, which uses only one column, a composite key uses a combination of multiple columns to represent the key fields. For example, using a combination of an employee’s first name, last name, and date of birth as a key would drastically reduce, but not completely eliminate, the chances of violating the uniqueness requirement. This provides a solution when a good simple key is not readily available.
