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,
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!