What is a Foreign Key?

A foreign key consists of a column that references another column in another table. This column that is being referred is most often a primary key of the referenced table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted. When you create a database to manage information for a business, it is common for tables in the database to have columns in common. In the following illustration, the CUSTOMER and ORDER tables share the CustID column.

The difference between the CustID field in the CUSTOMER table and its counterpart in the ORDER table is that the CustID field is the primary-key field for the CUSTOMER table but not for the ORDER table. When table ORDER contains a field that is the primary-key field in table CUSTOMER, that field in table ORDER is referred to as a foreign key. ^[1] In the context of relational database design, a foreign key is a fundamental concept that plays a critical role in establishing and enforcing relationships between tables. It is a field (or a collection of fields) in one table that uniquely identifies a row of another table. The primary purpose of a foreign key is to maintain referential integrity within the database.
Imagine a database of a university which has several tables like 'Students', 'Courses', and 'Enrollments'. The 'Students' table has a unique identifier for each student, typically called the primary key, say 'StudentID'. The 'Courses' table similarly has a primary key, say 'CourseID'. Now, the 'Enrollments' table, which keeps track of which students are enrolled in which courses, needs to refherence both 'Students' and 'Courses' tables, and here is where the foreign keys come into play. In the 'Enrollments' table, there would be two foreign keys: 1) one linking 'StudentID' from the 'Students' table and 2) another linking 'CourseID' from the 'Courses' table. These foreign keys establish a link between the 'Enrollments' table and the other two tables. They ensure that for every record in the 'Enrollments' table, there is a corresponding valid student in the 'Students' table and a valid course in the 'Courses' table.
Furthermore, foreign keys are pivotal in ensuring data integrity, which prevent actions that would leave the database in an inconsistent state. For instance, if a foreign key constraint is in place, you cannot add a record to the 'Enrollments' table with a 'StudentID' that doesn't exist in the 'Students' table. Similarly, it prevents deleting a student from the 'Students' table if there are still enrollments linked to that student in the 'Enrollments' table, unless those enrollments are also deleted or modified. In database design, defining foreign keys involves specifying the relationship between tables, which can be one-to-one, one-to-many, or many-to-many. This specification includes enforcing referential integrity constraints that dictate how the database should react to attempts to delete or modify linked data, such as cascading deletes or updates. Overall, foreign keys are essential in relational database design for maintaining the accuracy and consistency of the data within the database. They help in structuring the database in a way that accurately reflects the relationships among different entities, like students, courses, and enrollments in our university database example.

When a child table contains a column (or concatenation of columns) that is the same as the primary key of the parent table, that column is called a foreign key. The matching of "foreign key values" to "primary key values" represents data relationships in a relational database. As far as the user of a relational database is concerned, there are no structures that show relationships between tables other than the matching column's values.
Note: The relationships in a relational database are between the logical constructs, which are the tables and nothing else. Such structures make absolutely no assumptions about physical storage. Foreign keys may be part of a concatenated primary key, or they may not be part of their table's primary key at all. Consider, for example, a pair of DistributedNetworks customers and orders relations:

customers(customer_numb, first_name, last_name, phone)
orders(order_numb, customer_numb, order_date)

The customer number column in the "orders table" is a foreign key that matches the primary key of the "customers table". It represents the one to-many relationship between customers and the orders they place. However, the customer number is not part of the primary key of its table but is a non-key attribute that is a "foreign key". Technically, foreign keys need not have values unless they are part of a concatenated primary key. However, in this particular database, Antique Opticals would be in serious trouble if customer numbers were null: There would be no way to determine which customer placed an order.

The following SQL creates a FOREIGN KEY on the "CustID" column when the "ORDER" table is created:

 
CREATE TABLE ORDER (
 OrderNo int NOT NULL,
 CustID int NOT NULL,
 OrderDate  DATE
 PRIMARY KEY (OrderNo),
 FOREIGN KEY (CustID) REFERENCES CUSTOMER(CustID)
);

The line between the CustID fields in the two tables represents the link between the two tables. Linking tables enables you to join the tables and combine the records that share values in the linked fields.

A primary-key field and a foreign-key field do not need to have the same name to be linkable. They do need to contain the same data. Remember, the relational database management system does not identify the link between the tables, the designer of the database tables does. The next lesson describes the concept of referential integrity.

Before you move on to the next lesson, click the Quiz link below to reinforce your understanding of foreign keys.
Relational Constructs - Quiz

[1]Foreign key: A field (or combination of fields) used to link tables; a corresponding primary key field occurs in the same database.