Class 14 of 58 • Database & Table Management (DDL)

SQL Foreign Key: Referential Integrity & Cascading Rules

Relational databases derive their power from connecting related entities. A Foreign Key (FK) is a column (or group of columns) in a child table that references the Primary Key of a parent table. Foreign keys enforce Referential Integrity, preventing orphan records and ensuring relational relationships remain synchronized.

Parent Table vs Child Table Defined

• Parent Table (Referenced): The table containing the candidate or primary key that is being referenced (e.g., departments).

• Child Table (Referencing): The table containing the foreign key pointing back to the parent (e.g., employees).

Hands-on Implementation: Department & Employee Linkage

-- 1. Create Parent Table: departments
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

-- 2. Create Child Table: employees with Foreign Key
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    dept_id INT,
    salary DECIMAL(10, 2),
    
    -- Define Foreign Key with Referential Actions
    CONSTRAINT fk_emp_department
        FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Referential Cascading Actions Explained

When a row in the parent table is deleted or updated, what should happen to referencing rows in the child table? SQL provides 4 standardized cascading options:

Action Clause	Engine Behavior on Parent Record Modification
CASCADE	Deleting/updating the parent automatically deletes or updates all matching rows in the child table. Ideal for dependent child items (e.g., deleting an Order automatically deletes its Order_Items).
RESTRICT / NO ACTION	The default safety behavior! Rejects the parent deletion/update with an error if any referencing rows exist in the child table.
SET NULL	Sets the foreign key column in the child table to `NULL` when the parent is deleted. Requires that the FK column allows NULL.
SET DEFAULT	Resets the child foreign key to its defined default value upon parent deletion.

Testing Referential Integrity in Practice

-- Populate parent departments
INSERT INTO departments VALUES (10, 'Engineering'), (20, 'Marketing');

-- Populate child employee
INSERT INTO employees (emp_name, dept_id, salary) VALUES ('Aman Verma', 10, 85000);

-- Attempt to insert an employee with non-existent dept_id 99
INSERT INTO employees (emp_name, dept_id, salary) VALUES ('Rohan', 99, 50000);
-- Result: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

Congratulations! You have mastered Module 2 (DDL & Integrity). Now let us advance to Module 3 and learn Data Manipulation starting with the SQL INSERT Statement!

Frequently Asked Questions (FAQ)

1. What is Referential Integrity in relational databases?

Referential integrity is a database constraint that ensures that a foreign key value in a child table must always point to a valid, existing primary key in the parent table. It prevents 'orphan records' from being created.

2. What is the difference between CASCADE and RESTRICT on delete?

ON DELETE CASCADE automatically deletes all matching child rows when the parent row is deleted. ON DELETE RESTRICT (or NO ACTION) throws an error and blocks deletion of the parent row as long as referencing child rows exist.

3. Can a Foreign Key column contain NULL values?

Yes! If the foreign key column is defined as nullable, storing NULL indicates that the child record currently has no association with any parent record (e.g., an employee who has not yet been assigned to any department).