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

Output

 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

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.

SQL

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.

Multiple Choice Questions

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

A. Domain Independence B. Referential Integrity C. Atomicity D. Entity Cardinality Answer: B Explanation: Referential integrity ensures that relationships between tables remain consistent, preventing orphaned child records.

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

A. ON DELETE RESTRICT B. ON DELETE SET NULL C. ON DELETE CASCADE D. ON DELETE NO ACTION Answer: C Explanation: ON DELETE CASCADE instructs the database engine to automatically delete all dependent child records whenever the corresponding parent record is removed.

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`?

A. The employees are deleted automatically B. The query fails and throws a foreign key constraint violation error C. The employees' department is set to 0 D. The department is archived to a CSV file Answer: B Explanation: RESTRICT prohibits the deletion of a parent record as long as any child rows reference its primary key.

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

A. MyISAM B. Memory C. InnoDB D. CSV Answer: C Explanation: InnoDB is the only standard MySQL storage engine that actively enforces foreign key constraints and referential actions.

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`?

A. MySQL automatically converts both to BIGINT B. MySQL rejects table creation with a foreign key mismatch error C. MySQL ignores the sign difference D. The table creates but disables indexes Answer: B Explanation: Foreign key and parent key column data types must match exactly, including integer signedness (UNSIGNED).

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

PrevNext