Foreign Key in SQL 

A foreign key in SQL is a column or a set of columns in one table that links to the primary key of another table. It creates a relationship between the two tables, helping to maintain accurate and consistent data. In a relational database, information is often spread across multiple tables. Foreign keys help connect that data by matching values, making the database more organized and meaningful. In this blog, you will learn what foreign keys are, why they are important, and how to create them with clear steps and examples.

Table of Contents:

• What is a Foreign Key in SQL?
• Syntax of Foreign Key in SQL
• Types of Foreign Keys in SQL
• Creating a Foreign Key in SQL
• Dropping a Foreign Key Constraint in SQL
• Using ON DELETE and ON UPDATE with Foreign Keys in SQL
• Using Foreign Keys to Normalize SQL Databases
• Difference Between Primary Key and Foreign Key in SQL
• Common Challenges While Using Foreign Keys in SQL
• Best Practices for Using Foreign Keys in SQL
• Real-World Example of Foreign Key in SQL
• Conclusion

What is a Foreign Key in SQL?

A foreign key in SQL is a column (or group of columns) in a table that is a reference to the primary key in another table. It establishes a relationship between two tables. The table that has the foreign key is called the child table, and the table that has the primary key is called the parent table.

Importance of Foreign Keys

Foreign keys play a significant role in designing a database. Here are some of the reasons why they are important:

• Ensure Data Integrity: They ensure that values entered in the child table must exist in the parent table.
• Avoid Orphan Records: You cannot enter a record that points to a non-existent parent table.
• Support Table Relationships: Foreign keys are used to express one-to-one, one-to-many, or many-to-many relationships.
• Enhance Data Structure: Foreign keys help organize data, making it manageable and scalable.

Syntax of Foreign Key in SQL

This is the standard syntax used to define a foreign key while creating a table in SQL:

Syntax:

CREATE TABLE ChildTable (
    Column1 DataType,
    Column2 DataType,
    ...
    FOREIGN KEY (ColumnName) REFERENCES ParentTable(PrimaryKeyColumn)
);

Master SQL & Land Your Dream Data Job

Join our expert-led, hands-on SQL course designed to take you from beginner to pro

Explore Program

Types of Foreign Keys in SQL

Depending on the design of the table, a foreign key can be used differently. Here are the different types of foreign keys used in SQL:

1. Simple Foreign Key

This is the most common type. A simple foreign key is defined as a one-column relation from one table that references the primary key of a second table.

Let’s consider two tables: Departments as the parent table and Employees as the child table. A foreign key is defined on the DeptID column in the Employees table to link it with the DeptID column in the Departments table, as shown in the example below:

Example:

-- Parent Table
CREATE TABLE Departments (
  DeptID INT PRIMARY KEY,
  DeptName VARCHAR(50)
);
-- Child Table
CREATE TABLE Employees (
  EmpID INT PRIMARY KEY,
  EmpName VARCHAR(50),
  DeptID INT,
  FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

Explanation: In the above query, DeptID in the Employees table is a foreign key to the Departments table. The foreign key ensures that an employee must belong to an existing department.

2. Composite Foreign Key

A composite foreign key is a foreign key that uses two or more columns to define the relationship. This relationship is used when a single column is not sufficient to uniquely identify a record.

Example:

-- Parent Table
CREATE TABLE CourseSchedule (
  CourseID INT,
  SemesterID INT,
  PRIMARY KEY (CourseID, SemesterID)
);
-- Child Table
CREATE TABLE StudentEnrollments (
  StudentID INT,
  CourseID INT,
  SemesterID INT,
  FOREIGN KEY (CourseID, SemesterID) REFERENCES CourseSchedule(CourseID, SemesterID)
);

Explanation: This query creates a parent table, CourseSchedule, with a composite primary key (CourseID, SemesterID). The child table StudentEnrollments uses both columns as a foreign key to ensure students enroll only in scheduled course-semester combinations.

3. Foreign Key with Cascading Actions

This type of foreign key also contains ON DELETE CASCADE or ON UPDATE CASCADE actions. These actions specify what should happen to the child record if the record it is related to in the parent table is deleted or updated.

Example:

-- Parent Table
CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  CustomerName VARCHAR(50)
);

-- Child Table
CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE
);

Explanation: This query sets up a foreign key in the Orders table referencing Customers(CustomerID) with ON DELETE CASCADE. It ensures that when a customer is deleted, all their associated orders are automatically removed to prevent orphan records.

Creating a Foreign Key in SQL

Let’s look at the simple example of how to create a foreign key in SQL:

Step 1: Create the Parent Table

The Courses table is created to store course details. The CourseID column is defined as the primary key to ensure that each course has a unique identifier.

CREATE TABLE Courses (
  CourseID INT PRIMARY KEY,
  CourseName VARCHAR(50)
); 

Step 2: Create the Child Table

The Students table is created to store student information. The CourseID column is set as a foreign key to link each student to a course listed in the Courses table, establishing a relationship between the two tables.

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  StudentName VARCHAR(50),
  CourseID INT,
  FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)

);

Dropping a Foreign Key Constraint in SQL

This process removes an existing foreign key from a table using the ALTER TABLE statement. It is useful when you want to modify or delete the relationship between two tables to update the database structure.

Syntax:

ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

Example: If the foreign key in the Students table is named fk_course:

ALTER TABLE Students
DROP FOREIGN KEY fk_course;

Using ON DELETE and ON UPDATE with Foreign Keys in SQL

In SQL, when a record in the parent table is updated or deleted, we can define what changes must be made on the child table with the help of ON DELETE and ON UPDATE actions.

Syntax:

FOREIGN KEY (ColumnName)
REFERENCES ParentTable(PrimaryKeyColumn)
ON DELETE CASCADE
ON UPDATE CASCADE

Common Actions:

• CASCADE: Automatically updates or deletes related records.
• SET NULL: Sets the foreign key value to NULL in the child table.
• SET DEFAULT: Sets the value to a default (must be defined earlier).
• RESTRICT / NO ACTION: Prevents the action if child rows exist.

Using Foreign Keys to Normalize SQL Databases

Data normalization is a process of reducing repetitive data, which helps to improve efficiency. A foreign key in SQL helps to achieve normalization by linking smaller and related tables.

Example:

CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(100)
);

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(100),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

Explanation: Here, the DeptID in the employees table is a foreign key that helps to connect employees to their respective department, as this avoids repeating department names for every employee.

Difference Between Primary Key and Foreign Key in SQL

Let us explore the difference between a Primary key and a Foreign key in SQL.

Common Challenges While Using Foreign Keys in SQL

• Insert Restrictions: If the parent table does not have a matching value, the child table cannot insert that value. This helps keep the data accurate and connected.
• Delete Restrictions: You cannot delete a record from the parent table if it is being used in the child table, unless the foreign key is set with ON DELETE CASCADE to remove related records automatically.
• Complex Joins: Queries with multiple joins due to foreign keys can become slow and harder to read.
• Circular References: It is not uncommon for two tables to reference each other using foreign keys, which can complicate the data relationships.

 Best Practices for Using Foreign Keys in SQL

• Meaningful Names for Constraints: When you create a foreign key, give it a proper name so that users can understand.
• Avoid NULLs Unless Necessary: If your data design doesn’t allow, avoid using NULL values in foreign key columns unless required.
• Add Indexes Where Appropriate: You can create indexes on foreign key columns to help improve query performance.
• Use Careful ON DELETE and UPDATE Actions: Be careful either letting relationships cascade or restricting them based on how you intend to relate your data.
• Test with Sample Data: Before enforcing foreign key relationships on real data, test them with dummy data using inserts, updates, and deletes to make sure everything is performing correctly.

Real-World Example of Foreign Key in SQL

Let’s see how foreign keys help connect tables and keep data accurate in real-world use.

1. Hospital Management System

A hospital keeps track of its patients and doctors and logs which doctors are assigned to patients.

Example:

-- Creating the Patients table
CREATE TABLE patients (
    patient_id INT PRIMARY KEY,
    patient_name VARCHAR(100)

);
-- Creating the Doctors table
CREATE TABLE doctors (
    doctor_id INT PRIMARY KEY,
    doctor_name VARCHAR(100)

);
-- Creating the Assignments table to link Patients and Doctors
CREATE TABLE assignments (
    assignment_id INT PRIMARY KEY,
    patient_id INT REFERENCES patients(patient_id),
    doctor_id INT REFERENCES doctors(doctor_id)

);
-- Inserting data into the Patients table
INSERT INTO patients (patient_id, patient_name) VALUES
    (1, 'Alice Johnson'),
    (2, 'Bob Williams');
-- Inserting data into the Doctors table
INSERT INTO doctors (doctor_id, doctor_name) VALUES
    (201, 'Dr. Emily Carter'),
    (202, 'Dr. Mark Lee');
-- Inserting data into the Assignments table
INSERT INTO assignments (assignment_id, patient_id, doctor_id) VALUES
    (1, 1, 201),
    (2, 2, 202);
-- Displaying Results
SELECT * FROM patients;
SELECT * FROM doctors;
SELECT * FROM assignments;

Output:

Explanation: Here, three tables are created, which are used to manage the patients, doctors, and their assignments. Foreign keys in the assignments table make sure that only valid patient and doctor IDs are linked.

Start Learning SQL for Free

Build your foundation in SQL with our beginner-friendly, zero-cost course.

Explore Program

Conclusion

In relational databases, foreign keys are essential for building structured and dependable systems. They maintain relationships between tables by linking related data and ensuring consistency and accuracy across the database. Whether you are handling student enrollments, customer orders, or employee records, foreign keys keep the data organized and meaningful. They help prevent errors by blocking invalid entries and make it easier to run queries that involve multiple tables. When used properly, foreign keys lead to cleaner data, easier maintenance, and stronger overall database design. This makes your system more reliable and easier to grow in the future.

Take your skills to the next level by enrolling in the SQL Course today and gaining hands-on experience. Also, prepare for job interviews with SQL Interview Questions prepared by industry experts

Foreign Key in SQL – FAQs