KEY FIELD

 KEY FIELD

A key is a data item that allows us to uniquely identify individual occurrences or an entity type. You can sort and quickly retrieve information from a database by choosing one or more fields (ie attributes) to act as keys. For instance, in a student's table you could use a combination of the last name and first name fields (or perhaps last name, first name and birth dates to ensure you identify each student uniquely) as a key field. 

There are several types of key field:

• Primary Key

• Secondary Key

• Foreign key

• Simple key

• Compound key

• Composite key

Primary Key

A primary key consists of one or more attributes that distinguishes a specific record from any other. For each record in the table the primary key acts like a driver's licence number or a national insurance number, only one number exists for each person.

For example, your student number is a primary key as this uniquely identifies you within the college student records system. An employee number uniquely identifies a member of staff within a company. 

An IP address uniquely addresses a PC on the internet.

A primary key is mandatory. That is, each entity occurrence must have a value for its primary key.

Secondary Key

An entity may have one or more choices for the primary key. Collectively these are known as candidate keys. One is selected as the primary key. Those not selected are known as secondary keys.

For example, an employee has an employee number, a National Insurance (NI) number and an email address. If the employee number is chosen as the primary key then the NI number and email address are secondary keys. However, it is important to note that if any employee does not have a NI number or email address (ie: the attribute is not mandatory) then it cannot be chosen as a primary key.

Foreign Key

A foreign key is one or more attribute in one entity, which enables a link (or relationship) to another entity. That is, a foreign key in one entity links to a primary key in another entity. However, if the business rules permit, a foreign key may be optional.

For example, an employee works in a department. The department number column in the employee entity is a foreign key, which links to the department entity.

Foreign keys will be explained in more detail when we explore the normalisation process later in this section.

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. Itcan 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 probablynot 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 probablyis 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

http://www.databasedev.co.uk/what_is_a_key.html

http://en.wikipedia.org/wiki/Key_field

http://www.databasedev.co.uk/select_primary_key.html