Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/plsql/pl-sql-on-delete-cascade/ below:

PL/SQL ON DELETE CASCADE - GeeksforGeeks

The ON DELETE CASCADE option in PL/SQL is used to automatically delete rows from a child table when the corresponding row in the parent table is deleted. This feature helps maintain referential integrity by ensuring that related records in child tables are removed in sync with changes in the parent table.

This is particularly useful in relational databases where entities are linked, and deleting a parent record should also remove its dependent records. This article will explore the concept of ON DELETE CASCADE in PL/SQL, and explain examples with their syntax, and output.

The ON DELETE CASCADE constraint is applied to foreign key relationships to specify that when a row in the parent table is deleted, all corresponding rows in the child table should be automatically deleted. This ensures consistency and prevents orphaned records.

Syntax:

CREATE TABLE child_table (

child_id INT PRIMARY KEY,

parent_id INT,

FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id) ON DELETE CASCADE

);

Explanation:

This syntax defines a child_table with a primary key column (child_id) and a foreign key column (parent_id). The foreign key ensures referential integrity by linking records in child_table to parent_table, and the ON DELETE CASCADE option ensures that any related child records are automatically removed when the corresponding parent record is deleted.

The ON DELETE CASCADE clause is used in PL/SQL to automatically delete related records in a child table when the corresponding record in the parent table is deleted.

This ensures referential integrity by preventing orphaned records in the child table.

The departments table is created with two columns: dept_id, which is the primary key, and dept_name, which stores the department names. The dept_id uniquely identifies each department.

The CREATE TABLE statement creates the departments table with dept_id as the primary key. The INSERT INTO statements add three records into the departments table, representing three departments: HR, Finance, and IT.

Query:

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);
INSERT INTO departments (dept_id, dept_name) VALUES (1, 'HR');

INSERT INTO departments (dept_id, dept_name) VALUES (2, 'Finance');
INSERT INTO departments (dept_id, dept_name) VALUES (3, 'IT');

Output:

Explanation:

• The output will be the successful creation of the departments table and the insertion of three records into it, with dept_id values 1, 2, and 3 corresponding to the HR, Finance, and IT departments respectively.
  
• The output is not directly visible in the database but can be confirmed by querying the departments table.

The employees table is created with a foreign key constraint on dept_id, which references the departments table.

The ON DELETE CASCADE clause ensures that if a department is deleted from the departments table, all related records in the employees table are automatically deleted. The INSERT statements add five employees, each linked to a department by dept_id

Query:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE CASCADE
);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (1, 'Alice', 1);

INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (2, 'Bob', 2);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (3, 'Charlie', 3);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (4, 'David', 1);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (5, 'Eve', 2);

Output:

Explanation:

• The output displays a table of employees along with their corresponding department IDs. Each emp_id is unique, showing the employee's name (emp_name) and the department they belong to (dept_id).
  
• The data reflects the successful insertion of employee records, with departments linked by foreign key constraints.

Deleting a department will automatically trigger the deletion of all employees associated with that department due to the ON DELETE CASCADE clause in the employees table.

In this case, employees "Alice" and "David," who were linked to dept_id = 1 (HR department), are also deleted. This action ensures referential integrity, preventing orphaned records in the employees table that would no longer have a corresponding department.

Query:

DELETE FROM departments WHERE dept_id = 1;

Output:

Explanation:

• When we delete the department with dept_id = 1 from the departments table, the ON DELETE CASCADE clause automatically removes all employees in the employees table associated with that department.
  
• All employees linked to the deleted department are removed.
  
• The department no longer exists in the departments table.

When deleting multiple departments, the cascade delete feature ensures that all employees related to those departments are also removed.

In this query, deleting departments with dept_id values 1 and 2 triggers the ON DELETE CASCADE action in the employees table. This operation ensures that the database remains consistent and no employees are left without a valid department.

Query:

DELETE FROM departments WHERE dept_id IN (1, 2);

Output:

Explanation:

• Employees associated with the deleted departments are automatically removed.
  
• Specifically, employees "Alice," "David," "Bob," and "Eve" are deleted because their associated departments (HR and Finance) were removed
  
• Only departments not targeted by the delete operation remain.
  
• Only the IT department and its associated employee "Charlie" remain in the respective tables.

If we try to delete a department that doesn't exist, the ON DELETE CASCADE clause has no effect because there are no related records to delete.

In this scenario, attempting to delete a department with dept_id = 4 results in no changes because this department does not exist in the departments table. The ON DELETE CASCADE clause has no effect since there are no related records in the employees table to delete.

Query:

DELETE FROM departments WHERE dept_id = 4;

Output:

Explanation:

• No changes occur in the tables since the specified department does not exist.
  
• Data integrity is maintained without any unintended deletion
  
• Consequently, both the departments and employees tables remain unchanged, preserving data integrity without any unintended deletions.
  
• This ensures that only existing records are targeted by delete operations.

The ON DELETE CASCADE option is a valuable feature in SQL that maintains referential integrity by ensuring that child records are automatically deleted when their parent record is removed. This option simplifies data management and prevents orphaned records, making it easier to keep related data consistent.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4