The CASCADE option in Oracle SQL automatically propagates changes made to a parent table to its related child tables. When a CASCADE action is performed on a parent table, the corresponding changes will be automatically applied to all the child tables, making it easier to manage data consistency.
Here's an example of how to use CASCADE in Oracle SQL:
Let's say we have two tables, a parent table called "departments" and a child table called "employees". The "departments" table has a primary key column called "dept_id", and the "employees" table has a foreign key column called "dept_id" that references the "dept_id" column in the "departments" table.
To use CASCADE, we can define the foreign key constraint in the "employees" table with the CASCADE option as follows:
ALTER TABLE employees
ADD CONSTRAINT emp_dept_fk
FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
ON DELETE CASCADE;
This statement adds a foreign key constraint to the "employees" table with the name "emp_dept_fk" that references the "dept_id" column in the "departments" table. The ON DELETE CASCADE option ensures that when a row is deleted from the "departments" table, all related rows in the "employees" table will also be automatically deleted.
So if we execute the following statement to delete a row from the "departments" table:
DELETE FROM departments
WHERE dept_id = 10;
All the corresponding rows in the "employees" table that have a "dept_id" of 10 will be automatically deleted as well