Lets keep this as easy as possible for better understanding of the concepts. Point to Remember, the same definition, principle and naming applies equally to Entity Modelling and Normalisatio.

Keys, as name suggests, a part of a relational database and a important part of the structure of a table. They ensure each record within a table can be uniquely identified by one or a combination of fields within the table. Fields, are nothing but columns. They help enforce integrity and help identify the relationship between tables. There are three main types of keys:

• Candidate keys
• Primary keys
• Foreign keys.

There is also an alternative key or secondary key that can be used, as the name suggests, as a secondary or alternative key to the primary key

Super Key

A Super key is any combination of fields within a table that uniquely identifies each record within that table.

Candidate Key

 

A candidate is a subset of a super key. Lets say I have 5 super keys in a table then one or more than one can be a candidate key. A candidate key is a single field or the least combination of fields that uniquely identifies each record in the table. The least combination of fields distinguishes a candidate key from a super key. Every table must have at least one candidate key but at the same time can have several. In order to be eligible for a candidate key it must pass certain criteria.

•     It must contain unique values
•     It must not contain null values
•     It contains the minimum number of fields to ensure uniqueness
•     It must uniquely identify each record in the table

Once your candidate keys have been identified you can now select one to be your primary key

Primary Key

A primary key is a candidate key that is most appropriate to be the main reference key for the table. As its name suggests, it is the primary key of reference for the table and is used throughout the database to help establish relationships with other tables. As with any candidate key the primary key must contain unique values, must never be null and uniquely identify each record in the table.

Primary keys are mandatory for every table – this is not necessary though. Each record in table must have a value for its primary key. When choosing a primary key from the pool of candidate keys, it is always advisible to choose a single simple key as compared a composite key.

 

Foreign Key

A foreign key is generally a primary key from one table that appears as a field in another where the first table has a relationship to the second. In other words, if we had a table A with a primary key X that linked to a table B where X was a field in B, then X would be a foreign key in B.

Candidate key or Secondary Key or Alternative Key

A table may have one or more choices for the primary key. Collectively these are known as candidate keys as discuss earlier. One is selected as the primary key. Those not selected are known as secondary keys or alternative keys.

Simple Key

Any of the keys described before (ie primary, secondary or foreign) may comprise one or more fields, for example if firstName and lastName was our key this would be a key of two fields where as studentId is only one. A simple key consists of a single field to uniquely identify a record. In addition the field in itself cannot be broken down into other fields, for example, studentId, which uniquely identifies a particular student, is a single field and therefore is a simple key. No two students would have the same student number.

Compound Key

A compound key consists of more than one field to uniquely identify a record. A compound key is distinguished from a composite key because each field, which makes up the primary key, is also a simple key in its own right. An example might be a table that represents the modules a student is attending. This table has a studentId and a moduleCode as its primary key. Each of the fields that make up the primary key are simple keys because each represents a unique reference when identifying a student in one instance and a module in the other.

 

Composite Key

A composite key consists of more than one field to uniquely identify a record. This differs from a compound key in that one or more of the attributes, which make up the key, are not simple keys in their own right. Taking the example from compound key, imagine we identified a student by their firstName + lastName. In our table representing students on modules our primary key would now be firstName + lastName + moduleCode. Because firstName + lastName represent a unique reference to a student, they are not each simple keys, they have to be combined in order to uniquely identify the student. Therefore the key for this table is a composite key.