Before moving ahead with this topic I’ll suggest you to read the blogs and watch my tutorials on foreign key and On Delete Set Null clause for a better understanding of this concept.
What is On Delete Cascade clause of foreign key?
On Delete Cascade as the name suggests deletes the dependent column entry in child table when there is any attempt of deleting the corresponding value in Parent table.
You can define foreign key with ON DELETE CASCADE clause either using create table or using alter table statement.
Example of On Delete Cascade Clause
We will again define two tables with the name as Authors and Books. Authors will be our parent table with two columns author_id and author_name. We will use author_id column as reference column for our foreign key constraint thus it’s mandatory for us to define this column either as primary key or unique key. Please read about Foreign Key for more information.
Let’s create our parent table Authors –
CREATE TABLE author
(
author_id NUMBER(3) CONSTRAINT athr_aid_pk PRIMARY KEY,
author_name VARCHAR2(30)
);
Read How To Define Primary Key Using Create Table
Now we will create our child table called Books. Child table books will consist of 3 columns – book_id, book_title and book_author_id. We will define foreign key on book_author_id column.
CREATE TABLE books
(
book_id NUMBER(3),
book_title VARCHAR2(30),
book_price NUMBER(3),
book_author_id NUMBER(3) CONSTRAINT bok_ai_fk REFERENCES author(author_id) ON DELETE CASCADE
);
Here we have our child table. In this table the column book_author_id will serve as the foreign key. If you will see the foreign key definition of this column then you will notice that at the end of the foreign key definition we specified our clause which is “On Delete Cascade”.
This is the concept behind On Delete Cascade clause in brief. You can watch my tutorial on the same for some practical examples.
Also, please do share it with your friends and help me spread the word. Thank you & have a great day!