Foreign Key & Referential Integrity0%

Foreign Key & Referential Integrity

Beginner12 min read•Updated: 2026-09-12

Foreign Keys & Referential Integrity: Linking Tables Robustly

Relational databases derive their real power from the ability to link records across multiple tables safely. A Foreign Key is an integrity constraint that establishes a direct parent-child relationship between two tables, ensuring that child records cannot reference non-existent parent records (Referential Integrity).

1. Understanding Parent and Child Tables

 PARENT TABLE: departments
 +---------+-------------------+
 | dept_id | dept_name         |  <-- dept_id is PRIMARY KEY
 +---------+-------------------+
 |       1 | Engineering       |
 |       2 | Human Resources   |
 +---------+-------------------+
            ^
            | Enforced by Foreign Key Constraint!
            |
 CHILD TABLE: employees
 +--------+-------------+---------+
 | emp_id | emp_name    | dept_id |  <-- dept_id is FOREIGN KEY
 +--------+-------------+---------+
 |    101 | Aarav       |       1 |  <-- Valid (References Engineering)
 |    102 | Diya        |       2 |  <-- Valid (References HR)
 |    103 | Rohan       |      99 |  <-- REJECTED! Error: Cannot add or update child row!
 +--------+-------------+---------+

2. Defining Foreign Key Constraints in SQL

1-- Parent Table:
2CREATE TABLE departments (
3    dept_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
4    dept_name VARCHAR(50) NOT NULL
5) ENGINE = InnoDB;
6 
7-- Child Table with Explicit Foreign Key:
8CREATE TABLE employees (
9    emp_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
10    emp_name VARCHAR(100) NOT NULL,
11    dept_id INT UNSIGNED, -- Must match parent data type and unsignedness!
12    
13    CONSTRAINT fk_employees_departments
14        FOREIGN KEY (dept_id) 
15        REFERENCES departments(dept_id)
16        ON DELETE RESTRICT
17        ON UPDATE CASCADE
18) ENGINE = InnoDB;

3. Referential Action Rules: What Happens on Delete/Update?

When a parent row is deleted or updated in departments, what should happen to the corresponding child rows in employees? You define this behavior using Referential Actions:

Action	What Happens to Child Rows when Parent is Deleted/Updated?	Typical Use Case
`RESTRICT` / `NO ACTION`	Blocks the delete/update of the parent if any child rows reference it. (Default behavior).	Preventing accidental deletion of departments that still contain active employees.
`CASCADE`	Automatically deletes or updates all child rows when the parent row is deleted or updated!	Deleting an `order` automatically cascades to delete all its `order_items`.
`SET NULL`	Sets the child column's value to `NULL` when the parent is deleted. (Child column must be nullable).	If a manager is deleted, set `manager_id = NULL` on their former reports.
`SET DEFAULT`	Sets the child column to its defined default value (rarely used in MySQL).	System fallbacks.

1-- Example: Cascading Delete for Orders and Items
2CREATE TABLE orders (
3    order_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
4    order_date DATE NOT NULL
5);
6 
7CREATE TABLE order_items (
8    item_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
9    order_id INT UNSIGNED NOT NULL,
10    product_name VARCHAR(100) NOT NULL,
11    
12    CONSTRAINT fk_items_order
13        FOREIGN KEY (order_id) REFERENCES orders(order_id)
14        ON DELETE CASCADE -- If order 50 is deleted, all items for order 50 vanish automatically!
15);

4. Prerequisites for Foreign Keys in MySQL

For a foreign key constraint to be successfully created:

1
Both tables must use the InnoDB storage engine (MyISAM ignores foreign keys!).
2
The referenced column in the parent table must be indexed (typically the Primary Key).
3
The data types, sign (UNSIGNED), and byte lengths of the foreign key and parent key must match exactly!

5. Best Practices & Common Pitfalls

Avoid Unintended Mass Deletions with CASCADE: Be extremely cautious with ON DELETE CASCADE. Deleting a single customer could silently wipe out 10 years of orders, invoices, and payments if cascaded blindly!
Index Foreign Key Columns: MySQL automatically creates an index on foreign key columns in child tables, ensuring that join queries and cascade checks execute rapidly.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What database concept guarantees that a foreign key value in a child table must point to an existing record in the parent table?

Question 2

Which referential action automatically deletes all child rows when the referenced parent row is deleted?

Question 3

What is the default behavior if an administrator attempts to delete a parent department record while employees still reference it under ON DELETE RESTRICT?

Question 4

Which storage engine must be used in MySQL for Foreign Key constraints to be actively enforced?

Question 5

What will happen if you attempt to create a foreign key on an INT UNSIGNED column pointing to a parent key defined as INT SIGNED?

Next Lesson

NOT NULL and DEFAULT Constraints

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Primary Key & AUTO_INCREMENT	NOT NULL and DEFAULT Constraints

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

NOT NULL and DEFAULT Constraints

Foreign Key & Referential Integrity

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Foreign Keys & Referential Integrity: Linking Tables Robustly

1. Understanding Parent and Child Tables

 PARENT TABLE: departments
 +---------+-------------------+
 | dept_id | dept_name         |  <-- dept_id is PRIMARY KEY
 +---------+-------------------+
 |       1 | Engineering       |
 |       2 | Human Resources   |
 +---------+-------------------+
            ^
            | Enforced by Foreign Key Constraint!
            |
 CHILD TABLE: employees
 +--------+-------------+---------+
 | emp_id | emp_name    | dept_id |  <-- dept_id is FOREIGN KEY
 +--------+-------------+---------+
 |    101 | Aarav       |       1 |  <-- Valid (References Engineering)
 |    102 | Diya        |       2 |  <-- Valid (References HR)
 |    103 | Rohan       |      99 |  <-- REJECTED! Error: Cannot add or update child row!
 +--------+-------------+---------+

2. Defining Foreign Key Constraints in SQL

1-- Parent Table:
2CREATE TABLE departments (
3    dept_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
4    dept_name VARCHAR(50) NOT NULL
5) ENGINE = InnoDB;
6 
7-- Child Table with Explicit Foreign Key:
8CREATE TABLE employees (
9    emp_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
10    emp_name VARCHAR(100) NOT NULL,
11    dept_id INT UNSIGNED, -- Must match parent data type and unsignedness!
12    
13    CONSTRAINT fk_employees_departments
14        FOREIGN KEY (dept_id) 
15        REFERENCES departments(dept_id)
16        ON DELETE RESTRICT
17        ON UPDATE CASCADE
18) ENGINE = InnoDB;

3. Referential Action Rules: What Happens on Delete/Update?

When a parent row is deleted or updated in departments, what should happen to the corresponding child rows in employees? You define this behavior using Referential Actions:

Action	What Happens to Child Rows when Parent is Deleted/Updated?	Typical Use Case
`RESTRICT` / `NO ACTION`	Blocks the delete/update of the parent if any child rows reference it. (Default behavior).	Preventing accidental deletion of departments that still contain active employees.
`CASCADE`	Automatically deletes or updates all child rows when the parent row is deleted or updated!	Deleting an `order` automatically cascades to delete all its `order_items`.
`SET NULL`	Sets the child column's value to `NULL` when the parent is deleted. (Child column must be nullable).	If a manager is deleted, set `manager_id = NULL` on their former reports.
`SET DEFAULT`	Sets the child column to its defined default value (rarely used in MySQL).	System fallbacks.

1-- Example: Cascading Delete for Orders and Items
2CREATE TABLE orders (
3    order_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
4    order_date DATE NOT NULL
5);
6 
7CREATE TABLE order_items (
8    item_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
9    order_id INT UNSIGNED NOT NULL,
10    product_name VARCHAR(100) NOT NULL,
11    
12    CONSTRAINT fk_items_order
13        FOREIGN KEY (order_id) REFERENCES orders(order_id)
14        ON DELETE CASCADE -- If order 50 is deleted, all items for order 50 vanish automatically!
15);

4. Prerequisites for Foreign Keys in MySQL

For a foreign key constraint to be successfully created:

1
Both tables must use the InnoDB storage engine (MyISAM ignores foreign keys!).
2
The referenced column in the parent table must be indexed (typically the Primary Key).
3
The data types, sign (UNSIGNED), and byte lengths of the foreign key and parent key must match exactly!

5. Best Practices & Common Pitfalls

Avoid Unintended Mass Deletions with CASCADE: Be extremely cautious with ON DELETE CASCADE. Deleting a single customer could silently wipe out 10 years of orders, invoices, and payments if cascaded blindly!
Index Foreign Key Columns: MySQL automatically creates an index on foreign key columns in child tables, ensuring that join queries and cascade checks execute rapidly.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What database concept guarantees that a foreign key value in a child table must point to an existing record in the parent table?

Question 2

Which referential action automatically deletes all child rows when the referenced parent row is deleted?

Question 3

What is the default behavior if an administrator attempts to delete a parent department record while employees still reference it under ON DELETE RESTRICT?

Question 4

Which storage engine must be used in MySQL for Foreign Key constraints to be actively enforced?

Question 5

What will happen if you attempt to create a foreign key on an INT UNSIGNED column pointing to a parent key defined as INT SIGNED?

Next Lesson

NOT NULL and DEFAULT Constraints

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Primary Key & AUTO_INCREMENT	NOT NULL and DEFAULT Constraints

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next