Programming MySQL What is Foreign Key in database: Functions, Usage, and Examples

What is Foreign Key in database: Functions, Usage, and Examples

What is Foreign Key in database: Functions, Usage, and Examples

A foreign key is an important element in relational database management systems, referring to a column or set of columns in a table that creates a relationship between the data in two tables.

Acting as a link, foreign keys ensure data consistency and integrity by confirming that each value in one table has a corresponding value in another table.

Foreign keys are crucial for maintaining data connectivity in the database. Although foreign keys do not need to be unique like primary keys, their role as references is essential for maintaining data integrity.

Without foreign keys, the potential for data inconsistencies increases, as there would be no control to ensure that data in associated tables remains in sync.

Explore more about foreign keys in this article. You’ll discover how they work, their functions, and examples of their use.

Also Read: 7 Types of Database Keys and Their Functions

What is a Foreign Key?

A foreign key is a vital element in a database management system (DBMS) that ensures relationships between tables. Its purpose is to handle the relationship between two tables, so the values ​​in each remain consistent.

The main difference between foreign keys and a primary keys lies in their functions. A primary key uniquely identifies each row in a table, while foreign keys maintains relationships between two different tables.

The foreign keys refers to primary key in another table, creating a structured relationship between tables in a database. Key characteristics of the oreign keys include:

  • It must refer to a column or set of columns as a primary key in a different table.
  • Allows duplicate values, unlike primary keys which must be unique.
  • Prevents transactions that could cause inconsistent data between table relations.

When to Use a Foreign Key

Conditions where foreign keys are beneficial include:

  • When relating data in one table to data in another.
  • When ensuring referential integrity between tables.
  • When the database is designed to manage relationships between multiple entities.

How Foreign Keys Work

Foreign keys work by enforcing data integrity through relationships between tables. When data is entered or updated, the system automatically verifies that the value in the foreign key column has a corresponding match in the related primary key column.

This process ensures that every relationship remains valid and that no data is isolated or unconnected.

Also Read: Import MySQL database by command line and phpMyAdmin

Functions of a Foreign Key

Foreign keys play several essential roles in database management:

  1. Maintain data integrity: Foreign keys ensure that referenced data isn’t deleted arbitrarily, preserving data stability and consistency between tables.
  2. Support relationships between tables: Foreign key columns allow tables to be linked, maintaining relational information within the database.
  3. Allow cascade operations: Foreign keys enable changes in one table to propagate to related tables, supporting cascading operations automatically.
  4. Prevent deletion of referenced data: Foreign keys prevent deletion or modification of data that references other tables, thereby maintaining referential integrity.
  5. Reduce data duplication: Foreign keys help eliminate unnecessary data repetition, enhancing storage efficiency.
  6. Simplify complex queries: They support query creation, allowing easier and more structured data access, even in complex situations.

Example of Using a Foreign Key

Before using foreign keys, ensure the table has a primary key to reference. It’s also essential to match data types between the foreign key and primary key columns.

Also Read: What is Unique Key in DBMS? Functions, Uses, and How It Works

Example #1: Using Foreign Keys in CUstomers and orders

For instance, the “Orders” table can have a foreign key that refers to the primary key in the “Customers” table. If the “Customers” table has the “CustomerID” column as the primary key, then the “Orders” table can use the “CustomerID” column as the foreign key:


CREATE TABLE Customers (
    CustomerID int NOT NULL,
    CustomerName varchar(255),
    PRIMARY KEY (CustomerID)
);

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int,
    CustomerID int,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

#2: demonstrating the practical use of a foreign key in database design.

Foreign keys example using the “Products” and “Categories” tables

Imagine we have two tables in a retail database:

  1. Categories: This table defines different categories of products (like Electronics, Clothing, and Furniture).
  2. Products: This table contains details about individual products that belong to each category.

Step 1: Create the Categories Table

In the Categories table, each category has a unique identifier (CategoryID), which serves as the primary key. This table also includes a CategoryName column to describe the type of products in that category.


CREATE TABLE Categories (
    CategoryID int NOT NULL,         -- Unique ID for each category
    CategoryName varchar(255),       -- Name of the category (e.g., "Electronics")
    PRIMARY KEY (CategoryID)         -- Primary key constraint
);
  • CategoryID: An integer that uniquely identifies each category (e.g., 1 for Electronics, 2 for Clothing).
  • CategoryName: Describes each category in human-readable terms.

Step 2: Create the Products Table with a Foreign Key

The Products table contains details about individual products. Each product belongs to a category, so we include a CategoryID column in this table to reference the CategoryID in the Categories table, creating a foreign key relationship.


CREATE TABLE Products (
    ProductID int NOT NULL,          -- Unique ID for each product
    ProductName varchar(255),        -- Name of the product (e.g., "Smartphone")
    Price decimal,                   -- Price of the product
    CategoryID int,                  -- Category ID linking to the Categories table
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) -- Foreign key constraint
);
  • ProductID: An integer that uniquely identifies each product.
  • ProductName: Describes the product name (e.g., “Smartphone,” “Sofa”).
  • Price: Stores the price of the product.
  • CategoryID: This is the foreign key that links each product to a specific category in the Categories table.

How the Foreign Key Works in this Example

The foreign key constraint on CategoryID in the Products table ensures that each product is linked to a valid category. If a product record is added with a CategoryID that doesn’t exist in the Categories table, the database will reject the entry, preventing inconsistencies.

For example:

  • Categories Table:
    • (CategoryID: 1, CategoryName: "Electronics")
    • (CategoryID: 2, CategoryName: "Clothing")
  • Products Table:
    • (ProductID: 101, ProductName: "Smartphone", Price: 699.99, CategoryID: 1) — links to the “Electronics” category.
    • (ProductID: 102, ProductName: "T-Shirt", Price: 19.99, CategoryID: 2) — links to the “Clothing” category.

Benefits of This Relationship

1. Data Integrity: The foreign key ensures that each product has a valid category. If a category is deleted, any related products will either prevent the deletion or cascade, depending on settings, maintaining data consistency.

2. Easier Querying: This relationship allows you to quickly retrieve all products within a specific category by joining the two tables. For example, to find all products in the “Electronics” category, you could use:


SELECT ProductName, Price
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
WHERE Categories.CategoryName = 'Electronics';

3. Organized Data Structure: The foreign key relationship enables a structured approach to data storage, making it easier to manage, categorize, and analyze product data.

This example highlights the importance of foreign keys in relational databases, ensuring a robust connection between tables that supports data integrity, organization, and ease of data access

Now You Know What a Foreign Key Is!

Foreign keys are crucial elements that ensure referential integrity between tables in a relational database, maintaining consistent data between tables.

The primary function of a foreign key is to connect data from different tables, reinforcing the organization and reliability of stored data. Proper use of foreign keys effectively and efficiently upholds data integrity.

Leave a Reply