DBMS - Foreign Key

Quiz

Foreign keys allow us to create meaningful relationships between tables while we are maintaining consistency and data integrity. In this chapter, we will elaborate the concept of foreign keys with examples and practical applications to give you a good understanding of foreign keys and how they are applied.

What are Foreign Keys?

A foreign key is an attribute or a set of attributes in one table that refers to the primary key of another table. By doing this, it creates a link between the two tables. Foreign keys are quite useful in maintaining relational structure and enforcing data consistency.

Imagine we have two tables −

STUDENT Table − (STUDENT_ID (Primary Key), NAME, ADDRESS, AGE, DOB)
COURSES Table − (COURSE_NAME, INSTRUCTOR, REFERENCE_ID (Foreign Key))

In this example, the REFERENCE_ID column is special. In the COURSES table, it refers to the STUDENT_ID column in the STUDENT table. This relationship shows that any course entry in the COURSES table is linked to a valid student in the STUDENT table.

Importance of Foreign Keys

Foreign keys serve several critical purposes in database management as listed below −

Data Integrity − Foreign keys are used for data in related tables remains consistent. For example, if a course references a student through the foreign key, that student must exist in the STUDENT Now if someone tries to delete or modify a student entry referenced in the COURSES table, the database throws an error. This is maintaining data integrity.
Establishing Relationships − The primary purpose of foreign keys is to link tables. Now they help in creating meaningful relationships. Like such as a student being associated with specific courses. Now an order being associated with a customer.
Avoiding Redundancy − This is another reason. Foreign keys reduce the need to duplicate data. Instead of storing full student details in the COURSES table, we can only store the REFERENCE_ID, which links to the STUDENT_ID in the STUDENT
Query Optimization − In database designing, use foreign keys to optimize query execution. The relationships between tables allow the database engine to retrieve data more efficiently during complex queries.
Enforcing Business Rules − By using foreign keys, we can enforce business rules. This is quite interesting. For example, if a student is removed from the STUDENT table, their associated courses in the COURSES table must also be removed or invalidated.

Syntax for Creating and Deleting Foreign Keys

Foreign keys are defined during table creation or added later. Let us see some SQL syntax for them −

Creating a Foreign Key

Let's understand the process of creating a foreign key −

CREATE TABLE COURSES (
    COURSE_NAME VARCHAR(20),
    INSTRUCTOR VARCHAR(20),
    REFERENCE_ID INT,
    CONSTRAINT FK_REFER FOREIGN KEY (REFERENCE_ID)
    REFERENCES STUDENT(STUDENT_ID)
);

In this example, the REFERENCE_ID column in the COURSES table references the STUDENT_ID column in the STUDENT table. The keyword CONSTRAINT is used to name the foreign key constraint.

Deleting a Foreign Key

To remove a foreign key, you have to use the ALTER TABLE statement −

ALTER TABLE COURSES
DROP FOREIGN KEY FK_REFER;

This command deletes the foreign key constraint named FK_REFER from the COURSES table.

Summary of Foreign Key Implementation

Here is a summary of the steps for implementing foreign keys −

Create the Primary Key Table − To create a foreign key, we must start by creating the table that will serve as the reference, ensuring it has a primary key.
Define the Foreign Key Table − Create the table that includes the foreign key column, referencing the primary key column from the first table.
Test the Relationship − Insert data into both the tables and test the relationship to ensure integrity is maintained.
Drop or Modify Constraints − Use the ALTER TABLE command to update or remove foreign key constraints when necessary.

Real-World Example of Foreign Keys

To make this concept even clearer, let us see how foreign keys work in a Student-Courses database system.

Step 1: Creating the Database

First, we create a new database called MYDB −

CREATE DATABASE MYDB;
USE MYDB;

Step 2: Creating the Tables

Next, we create the STUDENT and COURSES tables.

Here is the STUDENT Table −

CREATE TABLE STUDENT (
    STUDENT_ID INT PRIMARY KEY,
    NAME VARCHAR(20),
    ADDRESS VARCHAR(20),
    AGE INT,
    DOB DATE
);

Here is the COURSES Table −

CREATE TABLE COURSES (
    COURSE_NAME VARCHAR(20),
	INSTRUCTOR VARCHAR(20),
    REFERENCE_ID INT,
    CONSTRAINT FK_REFER FOREIGN KEY (REFERENCE_ID)
    REFERENCES STUDENT(STUDENT_ID)
);

Step 3: Linking the Tables

By using the REFERENCE_ID column in the COURSES table, we are referencing the STUDENT_ID column in the STUDENT table. We establish a relationship between the two tables. It shows that every course in the COURSES table must be linked to an existing student.

Why Do We Need Foreign Keys in DBMS?

Foreign keys address real challenges in database management. Listed below are some of the key aspects in DBMS where foreign keys play an important role −

Ensuring Data Consistency − Foreign keys prevent mismatches in data. For example, we cannot add a course for a student who does not exist in the STUDENT
Simplifying Data Management − With foreign keys, the data is easier to manage. Instead of duplicating the same data across tables, we maintain relationships and ensure that updates take place in one place.
Preventing Unauthorized Changes − Foreign keys add a layer of security by preventing unauthorized deletions or updates. For example, if a student is associated with a course, their record in the STUDENT table cannot be deleted until the relationship is resolved.

Self-Referencing Foreign Keys

Foreign keys can also reference the columns within the same table. This is called a self-referencing foreign key. For example, in an employee hierarchy, we might have a table where each employee has a MANAGER_ID column that references the EMPLOYEE_ID column within the same table.

Conclusion

In this article, we covered how foreign keys work and why they are essential in databases. We explored how foreign keys establish relationships between tables and ensure data consistency. We explained the syntax for creating and deleting foreign keys and demonstrated their application with a real-world example. We also looked at the importance of foreign keys in maintaining data integrity and reducing redundancy.

Print Page