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 –
CREATE TABLE author
author_id NUMBER(3) CONSTRAINT athr_aid_pk PRIMARY KEY,
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_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