Referential action - On Delete Cascade

By Manish Sharma

On Delete Cascade.

On Delete cascade clause of Foreign Key Explained in Detail

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!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

SQL Script used in Video and in this article

Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn

         View Manish Sharma's profile on LinkedIn