Enforcing referential integrity in PL/SQL trigger

PL/SQL Trigger: Exercise-4 with Solution

Write a code in PL/SQL to develop a trigger that enforces referential integrity by preventing the deletion of a parent record if child records exist.

Sample Solution:

PL/SQL Code:

-- Create the 'departments' table
CREATE TABLE departments (
department_id NUMBER PRIMARY KEY,
department_nameVARCHAR2(50)
);

-- Create the 'employees' table with a foreign key reference
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_nameVARCHAR2(50),
last_nameVARCHAR2(50),
department_id NUMBER,
    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments (department_id)
);

-- Create a trigger to enforce referential integrity
CREATE OR REPLACE TRIGGER prevent_parent_deletion
BEFORE DELETE ON departments
FOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
    -- Check if there are any associated child records
    SELECT COUNT(*) INTO v_count FROM employees WHERE department_id = :OLD.department_id;

    -- If child records exist, raise an error
    IF v_count> 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Cannot delete department with associated employees.');
    END IF;
END;
/

Let's see how the trigger functions:

PL/SQL Code:

-- Insert sample department and employee records
INSERT INTO departments (department_id, department_name) VALUES (1, 'Sales');
INSERT INTO employees (employee_id, first_name, last_name, department_id) VALUES (1, 'Ben', 'Turner', 1);

-- Try to delete the department with associated employees
DELETE FROM departments WHERE department_id = 1; -- This will raise an error

-- Delete the employee first
DELETE FROM employees WHERE employee_id = 1;

-- Now, delete the department
DELETE FROM departments WHERE department_id = 1; -- This will work

Flowchart:

Explanation:

The said code in PL/SQL that demonstrates the implementation of a PL/SQL trigger that enforces referential integrity by preventing the deletion of parent records from the 'departments' table if associated child records exist in the 'employees' table.

Assume that two tables 'departments' and 'employees' are involved with this PL/SQL code.

The trigger 'prevent_parent_deletion' is created for the 'departments' table using the BEFORE DELETE event and fires before a deletion operation is performed on the 'departments' table. It uses a local variable v_count to store the count of associated child records in the 'employees' table.

If child records are found (v_count> 0), the trigger raises an application error with the code -20001 and a message indicating that the department cannot be deleted due to associated employees.

When an attempt is made to delete the department with department_id 1 if there is an associated employee (Ben Turner) in the 'employees' table, the trigger fires and raises an error, preventing the deletion of the department.

To proceed with the department deletion, the associated employee (Ben Turner) is first deleted from the 'employees' table.

After the associated child record is deleted, the department deletion will be success.

Previous:Tracking Deleted Rows with a PL/SQL Trigger.
Next: Preventing duplicate values using PL/SQL triggers.