Relational Databases: Foreign Keys

You already know that a relation between two tables is created by assigning a common field to the two tables (see Relational Databases: Table Keys). This common field must be a primary key to one table. Consider a relation between a customer table and a sale table. The relation is not much good if instead of using the primary key, customer_code, in the sale table, you use another field that is not unique, such as the customer's first name. You would be unlikely to know for sure which customer made the sale in that case. So, in the table below, customer_code is called the foreign_key in the sale table; in other words, it is the primary key in a foreign table.

setting_foreign_keys

Foreign keys allow for something called referential integrity. What this means is that if a foreign key contains a value, this value refers to an existing record in the related table. For example, take a look at the tables below:

Lecturer table

CodeFirst NameSurname
1AnneCohen
2LeonardClark
3VusiCave

Course table

Course TitleLecturer Code
Introduction to Programming1
Information Systems2
Systems Software3

Referential integrity exists here, as all the lecturers in the course table exist in the lecturer table. However, let's assume Anne Cohen leaves the institution, and you remove her from the lecturer table. In a situation where referential integrity is not enforced, she would be removed from the lecturer table, but not from the course table, as shown below:

Lecturer table

CodeFirst NameSurname
2LeonardClark
3VusiCave

Course table

Course TitleLecturer Code
Introduction to Programming1
Information Systems2
Systems Software3

Now, when you look up who lectures Introduction to Programming, you are sent to a non-existent record. This is called poor data intregrity.

Foreign keys also allow cascading deletes and updates. For example, if Anne Cohen leaves, taking the Introduction to Programming Course with her, all trace of her can be removed from both the lecturer and course table using one statement. The delete cascades through the relevant tables, removing all relevant records.

Foreign keys can also contain null values, indicating that no relationship exists.

Comments

Comments loading...