What Are Foreign Keys in DBMS?

Introduction

Keys are an important part of database management systems (DBMS) like SQL. They help in ensuring data integrity and establishing relationships between tables. Among the different SQL keys, the foreign key is what maintains the relational structure of the database. It links various data points across tables to ensure smooth database operations. In this article, we will see how foreign keys work, what are the benefits of using them, and how you can use them in your DBMS.

If you’re just starting out to explore SQL, here’s a beginner’s guide to help you: SQL For Data Science: A Beginner Guide

Overview

• Understand what a foreign key is in DBMS.
• Understand how foreign keys work.
• Learn to create and use foreign keys in SQL through practical examples.
• Know the benefits of using foreign keys.

What is a Foreign Key?

A foreign key is what connects data points from different tables in a database. Basically, it is a column or a set of columns in a table that provides a link to the data in another table. It refers to the primary keys of the other table to create a relationship between the two. This connection is necessary to ensure referential integrity, i.e. to ensure that the data in one table corresponds accurately to the data in another.

How Foreign Keys Work

A foreign key constraint ensures that the value in the foreign key column must match an existing value in the referenced table’s primary key column. This relationship is similar to a parent-child relationship in programming, where the foreign key (child) references the primary key (parent).

Example:

Consider a database with the following tables:

users table with user_id as the primary key.
orders table with order_no as the primary key and user_id as a foreign key referencing the user_id in the users table.

This relationship ensures that each order is associated with a valid user.

Creating and Using Foreign Keys

Foreign keys can be defined during the creation of a table or added later using the ALTER TABLE statement.

Creating a Foreign Key

CREATE TABLE orders (
order_no INT PRIMARY KEY,
user_id INT,
product_sku INT,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (product_sku) REFERENCES books(product_sku)
);

In this example, the user_id in the orders table references the user_id in the users table, and product_sku references product_sku in the books table.

Adding a Foreign Key Using ALTER TABLE

ALTER TABLE orders
ADD FOREIGN KEY (user_id) REFERENCES users(user_id);

Benefits of Using Foreign Keys

1. Data Integrity: Foreign keys ensure that references between tables remain consistent, thereby maintaining data integrity.
2. Data Consistency: They prevent invalid data from being inserted into the foreign key column, ensuring data consistency.
3. Efficient Query Processing: Foreign keys optimize query processing by indexing the data to improve the retrieval of related data.
4. Data Security: They enhance data security by preventing unauthorized modifications or deletions of important data.
5. Database Maintenance: During database operations, foreign keys help maintain the integrity and consistency of data.

Referential Actions

When defining foreign keys, various actions can be specified to handle updates and deletions:

• CASCADE: Automatically updates or deletes the related rows in the child table when you delete or update the referenced row in the parent table.
• SET NULL: Sets the foreign key column to NULL when you delete the referenced row.
• SET DEFAULT: Sets the foreign key column to its default value when you delete or update the referenced row.
• RESTRICT: Prevents the deletion or updation of the referenced row if it is being referenced by another row.
• NO ACTION: Similar to RESTRICT but allows the operation to proceed if no referential integrity violations occur.

Example:

CREATE TABLE orders (
order_no INT PRIMARY KEY,
user_id INT,
product_sku INT,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (product_sku) REFERENCES books(product_sku) ON DELETE SET NULL ON UPDATE
CASCADE
);

Practical Implementation

To see foreign keys in action, consider the following steps to create a database and tables, and establish relationships using MySQL:

Create Database

CREATE DATABASE Bookstore;
USE Bookstore;

Create Tables with Foreign Keys

CREATE TABLE users (user_id INT PRIMARY KEY,
name VARCHAR(50)
);

CREATE TABLE books (
product_sku INT PRIMARY KEY,
title VARCHAR(100)
);

CREATE TABLE orders (
order_no INT PRIMARY KEY,
user_id INT,
product_sku INT,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (product_sku) REFERENCES books(product_sku)
);

Handling Updates and Deletions

DELETE FROM users WHERE user_id = 1;

— This will delete all orders related to user_id 1 if ON DELETE CASCADE is specified.

Conclusion

Foreign keys are important in DBMS like SQL. They help in maintaining data integrity and consistency across tables in the database. They play a vital role in managing complex relationships within a database by linking tables and enforcing constraints. Understanding and implementing foreign keys effectively can significantly enhance database design and operation. This would in turn lead to more robust and reliable applications.

Learn More: SQL: A Full Fledged Guide from Basics to Advanced Level

Frequently Asked Questions

K.C. Sabreena Basheer

Sabreena Basheer is an architect-turned-writer who's passionate about documenting anything that interests her. She's currently exploring the world of AI and Data Science as a Content Manager at Analytics Vidhya.