What is a Foreign Key in SQL?

A Foreign Key in SQL is a column or combination of columns used to create a relationship between two tables.

In simple words:

A foreign key connects one table with another table.

Why Foreign Key is Important

In real-world applications, data is usually divided into multiple tables.

Example:

Students table
Courses table
Orders table
Payments table

These tables must be connected properly.

Foreign keys help maintain these relationships.

Real-Time Example

Suppose a learning platform has:

Students table
Enrollments table

Students Table

ID	Name
1	Naresh
2	Rahul

Enrollments Table

Enrollment ID	Student ID	Course
101	1	MySQL
102	2	Spring Boot

Understanding the Relationship

Here:

student_id

inside enrollments table refers to:

id

inside students table.

This:

student_id

is called:

Foreign Key

Foreign Key Relationship Diagram

Students Table
------------------------------------------------
| ID (PK) | Name                              |
------------------------------------------------
| 1       | Naresh                            |
| 2       | Rahul                             |
------------------------------------------------
       |
       |
       v

Enrollments Table
------------------------------------------------
| Enrollment ID | Student ID (FK) | Course     |
------------------------------------------------
| 101           | 1               | MySQL      |
| 102           | 2               | Spring Boot|
------------------------------------------------

What is Primary Key?

A primary key uniquely identifies rows in a table.

Foreign keys usually reference primary keys.

Primary Key vs Foreign Key

Feature	Primary Key	Foreign Key
Purpose	Uniquely identifies rows	Creates relationships
Duplicates Allowed	No	Yes
NULL Allowed	No	Yes (usually)
Count Per Table	One	Multiple

How to Create Foreign Key

Foreign keys are created using:

FOREIGN KEY

SQL Syntax

CREATE TABLE enrollments (

    enrollment_id INT PRIMARY KEY,

    student_id INT,

    course VARCHAR(100),

    FOREIGN KEY (student_id)
    REFERENCES students(id)

);

Explanation

Column	Purpose
enrollment_id	Primary Key
student_id	Foreign Key
course	Course Name

What Does REFERENCES Mean?

The keyword:

REFERENCES students(id)

means:

student_id must exist in students table

Foreign Key Constraint Example

Suppose students table contains:

1

2

Valid Insert

INSERT INTO enrollments
VALUES (101, 1, 'MySQL');

Reason:

Student ID 1 exists

Invalid Insert

INSERT INTO enrollments
VALUES (102, 50, 'Spring Boot');

Error

Cannot add or update child row:
foreign key constraint fails

Why Error Happens

Because:

student_id = 50

does not exist in:

students table

Benefits of Foreign Key

Maintains data integrity
Prevents invalid data
Creates relationships between tables
Improves database consistency

Foreign Key Architecture

Parent Table
      |
      v
Primary Key
      |
      v
Child Table
      |
      v
Foreign Key

Parent Table vs Child Table

Table Type	Purpose
Parent Table	Contains Primary Key
Child Table	Contains Foreign Key

How Foreign Keys Help JOIN Operations

Foreign keys are commonly used with SQL JOINs.

Example Query

SELECT students.name,
       enrollments.course

FROM students

JOIN enrollments

ON students.id = enrollments.student_id;

Result

Name	Course
Naresh	MySQL
Rahul	Spring Boot

Types of Relationships Using Foreign Keys

1. One-to-One Relationship

User -> User Profile

2. One-to-Many Relationship

Course -> Students

3. Many-to-Many Relationship

Students <-> Courses

Real-Time Banking Example

Banking system tables:

customers

accounts

transactions

Relationship Example

accounts.customer_id

references:

customers.id

Real-Time E-Commerce Example

Orders table:

customer_id

references:

customers.id

Foreign Key Actions

Foreign keys support actions like:

CASCADE
SET NULL
RESTRICT

ON DELETE CASCADE Example

FOREIGN KEY (student_id)
REFERENCES students(id)
ON DELETE CASCADE

What Happens?

If student record is deleted:

Related enrollment records are automatically deleted

Advantages of Foreign Keys

Maintains referential integrity
Prevents invalid references
Supports relational database design
Improves data consistency

Challenges of Foreign Keys

Complex relationships
Performance overhead in large systems
Cascading delete risks

Best Practices

Use indexes on foreign keys
Use meaningful relationships
Avoid excessive cascading
Maintain proper normalization

Foreign Key Query Flow

Application
      |
      v
SQL Query
      |
      v
Foreign Key Validation
      |
      v
Database Consistency Check
      |
      v
Data Stored

Foreign Keys in Microservices

In microservices architecture:

Foreign keys are commonly used within individual service databases

Example

Order Service
      |
      v
orders table

payments table

payments.order_id

references:

orders.id

Real Production Example

In an e-commerce application:

Orders table references customers table
Payments table references orders table
Shipment table references orders table

This ensures:

Data consistency
Proper order tracking
Valid customer relationships

Professional Interview Answer

A foreign key in SQL is a column or combination of columns used to establish relationships between two tables. It references the primary key of another table and helps maintain referential integrity by ensuring that only valid data is stored. Foreign keys are widely used in relational databases to model real-world relationships such as customers and orders, students and enrollments, or users and payments. They play an important role in maintaining database consistency and supporting JOIN operations.

Why Interviewers Like This Answer

Clearly explains relationships
Includes practical SQL examples
Covers referential integrity
Shows relational database understanding
Includes real-world use cases

Frequently Asked Questions