✏️ Explanatory Question
Level: Basic — Tests your understanding of table relationships and data consistency.
A Foreign Key is a column (or set of columns) in one table that refers to the Primary Key of another table. It creates a link between the two tables — a parent (referenced) table and a child (referencing) table.
Referential integrity means the relationship between the tables always stays valid. A foreign key enforces this by ensuring you cannot insert a value in the child table that doesn't exist in the parent table, and you cannot delete a parent row that still has related child rows (unless a specific action is defined).
| Concept | Description |
|---|---|
| Parent Table | Contains the Primary Key being referenced (e.g., departments). |
| Child Table | Contains the Foreign Key that points to the parent (e.g., employees). |
| Enforced by | The InnoDB storage engine. |
| Guarantees | No orphan records — every child value must exist in the parent. |
-- Parent table
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
-- Child table with a foreign key + cascade rules
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
dept_id INT,
FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
ON DELETE CASCADE -- delete employees if their dept is deleted
ON UPDATE CASCADE -- update dept_id in employees if it changes
);
-- This works only if dept_id = 10 exists in departments
INSERT INTO departments VALUES (10, 'Engineering');
INSERT INTO employees VALUES (1, 'Rumman', 10);
-- This FAILS: dept_id 99 does not exist in the parent table
-- INSERT INTO employees VALUES (2, 'Ansari', 99);