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]

When a table contains a column (or concatenation of columns) that is the same as the primary key of a table, the 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 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 simple Antique Opticals 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 nonetheless a foreign key. Technically, foreign keys need not have values unless they are part of a concatenated primary key; they can be null. 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.

Question: What are the benefits of foreign keys in a relational database?
Foreign keys are an important component of a relational database, and provide several benefits. Here are some of the benefits of foreign keys in a relational database:

1. Data Integrity: Foreign keys help to enforce referential integrity, which ensures that data is consistent and accurate across related tables. Foreign keys ensure that data entered into a table matches the primary key of another table, preventing the creation of orphaned or invalid records.
2. Improved Query Performance: Foreign keys can improve query performance by allowing the database engine to efficiently join tables based on the relationships between them. This can help to reduce the amount of data that needs to be scanned and processed during query execution.
3. Simplified Database Design: Foreign keys can simplify database design by reducing the need for redundant data in multiple tables. Instead of duplicating data in multiple tables, a foreign key can be used to reference a single primary key in another table.
4. Easier Data Maintenance: Foreign keys can make it easier to maintain data in a database by providing a clear and structured way to manage relationships between tables. This can make it easier to update and modify data without introducing inconsistencies or errors.
5. Better Data Analysis: Foreign keys can improve data analysis by making it easier to analyze data across related tables. By using foreign keys to link tables together, analysts can perform more complex queries and generate more accurate reports.

Overall, foreign keys are an important tool for maintaining data integrity, improving query performance, simplifying database design, easing data maintenance, and supporting better data analysis. While foreign keys do not uniquely identify records in their tables, they do provide an important benefit. They enable you to create links between tables that share fields.

 
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 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.