What is Oracle SQL Cascade Delete | 5 Good Examples

Oracle SQL cascade delete is You have a Relation between tables then the Parent table record is deleted then referring child table records will be deleted. This is similar to a foreign key with CASCADE delete.

Once you create a table using the CREATE TABLE statement or modify the table by using the ALTER TABLE statement, we can add the feature Oracle SQL cascade delete into a table. The below examples show how to add or delete cascade SQL Oracle.

1: Simple Cascade Delete

This is the simplest example and syntax for CASCADE delete. I here used parent_table and child_table tables for mapping relations with each other and adding the delete cascade option.

--
CREATE TABLE parent_table (
    parent_id NUMBER PRIMARY KEY
);

CREATE TABLE child_table (
    child_id NUMBER PRIMARY KEY,
    parent_id NUMBER,
    FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id) ON DELETE CASCADE
);
--

As an example, if the Parent table has a record with primary key(parent_id) 10 and the child table has records child_id 101,102 with parent_id 10. Then record 10 is deleted from parent_table, and all related records (101,102) in child_table will be automatically deleted.

2: Cascade Delete Across Schemas

If you have a lot of schemas in your database You can have CASCADE delete different schemas, schema1.parent_table and schema2.child_table.

--
-- In schema1
CREATE TABLE schema1.parent_table (
    parent_id NUMBER PRIMARY KEY
);

-- In schema2
CREATE TABLE schema2.child_table (
    child_id NUMBER PRIMARY KEY,
    parent_id NUMBER,
    FOREIGN KEY (parent_id) REFERENCES schema1.parent_table(parent_id) ON DELETE CASCADE
); 
--

Now once you delete the record in the parent table on schema 1 and the reference table is available in schema2 still you can automatically delete the record from the child table in schema2 (schema2.child_table).

3: Cascade Delete with Multiple Levels

Oracle SQL cascade delete can operate across multiple levels Therefore you have to add relationships on each level. As an example, top_parent_table, parent_table, and child_table.

--
CREATE TABLE top_parent_table (
    top_parent_id NUMBER PRIMARY KEY
);

CREATE TABLE parent_table (
    parent_id NUMBER PRIMARY KEY,
    top_parent_id NUMBER,
    FOREIGN KEY (top_parent_id) REFERENCES top_parent_table(top_parent_id) ON DELETE CASCADE
);

CREATE TABLE child_table (
    child_id NUMBER PRIMARY KEY,
    parent_id NUMBER,
    FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id) ON DELETE CASCADE
);
--

Now When you delete records from the TOP parent table referring records from parent tale records will be deleted and then referring records from child_table deleted according to parent table foreign keys.

4: Self-Referencing Tables

In Some cases, there is a foreign key can be applied to the same table, Same table records can referring another record as a parent record. The most familiar example is the Employee Table. The manager is the parent of the employee. So will have many employees with parent managers Also there will be a record for the Manager as an Employee.

--
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    manager_id NUMBER,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id) ON DELETE CASCADE
);

--

These examples show the addition of foreign key constraints with the Cascade option. It helps with the maintainability of records but use it with proper validations. Let’s see what are the drawbacks of Oracle SQL cascade delete.

Problem On Cascade Delete

1: Unintended Data Deletion

Suppose There are articles written by users and later users going to delete. Then Associated table article has a foreign key as a user_id. If you want to keep the remaining article you can’t add CASCADE delete on the Article table.

--
CREATE TABLE users (
    user_id NUMBER PRIMARY KEY,
    username VARCHAR2(100)
);

CREATE TABLE articles (
    article_id NUMBER PRIMARY KEY,
    user_id NUMBER,
    post_content VARCHAR2(100),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);ASCADE 

--

According to this example accidentally delete a user from a table you will lose all the articles written by that user. So, you must add proper validation with CASCADE delete.

2: Orphaned Records

Let’s think about you have 3 tables Departments, Employees, and Work Tasks, The Below example shows the employee table has department _id as a foreign key with CASCADE but the Work_tasks table employee_id available as a foreign key but without Cascade. Then what happen once deleted one of the Department.

--
CREATE TABLE departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(100)
);

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(100),
    department_id NUMBER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE
);

-- Another table referencing employees
CREATE TABLE work_tasks (
    work_task_id NUMBER PRIMARY KEY,
    employee_id NUMBER,
    task_description VARCHAR2(100),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id) -- No CASCADE DELETE
);
--

Our Rule on Oracle SQL cascade delete is once the parent is deleted referring child is deleted according to the foreign key. In this case, once the department is deleted all the employees are deleted but Tasks are not deleted. So, they are remaining without Employees. That’s not good planning on a database.

3: Performance Impact

Oracle SQL cascade delete may have a performance impact on large databases with multilevel relationships. (Read our How To Performance Tuning In Oracle | 10 Tips  other related articles https://ennicode.com/?s=performance) There can be tables with millions of records and create hierarchies using different tables. In such cases delete records need to be more careful.

4: Circular Dependencies

oracle SQL cascade delete does not support between tables with circular dependencies. In this case, Foreign key is available in both tables referring other tables. Circular dependencies do NOT work on the Oracle database.

--
CREATE TABLE table1 (
    id NUMBER PRIMARY KEY,
    table2_id NUMBER,
    FOREIGN KEY (table2_id) REFERENCES table2(id) ON DELETE CASCADE
);

CREATE TABLE table2 (
    id NUMBER PRIMARY KEY,
    table1_id NUMBER,
    FOREIGN KEY (table1_id) REFERENCES table1(id) ON DELETE CASCADE
);
--

Finally this example returns an error once the record is deleted. So keep in mind Oracle does not support CASCADE deleted on circular dependencies