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 –

  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.

   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!