Key Fields

A key field uniquely identifies an occurrence of a record. An example of this would be a customer reference number in a customer table. Since no two customers should have the same customer reference number, this field can be called a simple key.

A compound key is formed when you need to combine two or more fields in a table to uniquely identify individual records. Sometimes no one field can uniquely identify every record, but combining two gives a unique reference. For example in a stock system it is not impossible that two different suppliers may use the same product code for different products. Combining the product code with the supplier reference number could then be used to give a compound key. In a compound key, the fields, which make up the compound key, are usually also simple keys in there own right in other tables. If one of the fields that make up the key is not a simple key, the combination of fields is usually called a composite key rather than a compound key.

In database systems such as MS Access, fields can be set as Primary Keys. A primary key must be the simple key field of a table and MS Access will enforce this. In MS Access this then enforces the uniqueness of the Primary Key field and will not allow duplicates fields to be entered. As well as this the table is automatically indexed on the Primary Key in Access, which is needed to allow the system to quickly check whether an entry is unique or not, before validating it or blocking it. The table can also be ordered on the Primary Key field by using this index.