SQL tutorials 20: On Delete Cascade Foreign Key By Manish Sharma (RebellionRider)

Key Takeaways

What's up internet? This is Manish from rebellionrider.com and today in this SQL tutorial we will learn about ondee casket clause of foreign key. But before that I'll suggest you to watch my previous video on foreign key and ondeel to have better understanding of this concept. You can also visit my website to read about onde cascade close link is in the description below. You can also download the SQL script which we will be going to create in this tutorial. Link is right here. I shared all my resources used in this tutorial using copy cloud. CopyCloud gives 15 GB of free cloud storage on joining but you can earn extra 5GB by using my referral link means you can get total 20 GB of free cloud storage. Pretty great, isn't it? To get extra 5GB, you just have to use my referral link which is in the description below to sign up. Then just verify your email address and download copy cloud on your desktop or laptop and you will receive 20 GB instantly. Okay, let's jump over SQL development. Now again we will be using parent table authors and child table books. Parent table author has two columns author ID and author name where author ID column is primary key. I have already inserted some data into this table to demonstrate on delete cascade clause. As this is a simple table with primary key, you can watch my SQL tutorial 17 on how to define primary key. And child table book has three column. Book ID, book title and book author ID. Book author ID is our foreign key column. For this tutorial, let's recreate the child table with ondee cascade clause of foreign key. So let's create the child table box. book call3_FK is a userdefined constraint name. You can give whatever you want. I just specify BK for table name books. Call three for uh column three as we are defining our foreign key over column three book author ID and FK is for foreign key as we have to define ondee casket clause on our foreign key. So we have to specify ondee casket right here. As column 3 book author ID is referenced from author ID column of table authors. Therefore, it must have the same data type and column width as of author ID column of author table. Execute table books created. Now we have to insert some data. As you can see, I'm going to insert five rows in our book table. Select them all and execute them all. We have five rows. Let's check. Here is our book table. Here is our book table with three column book ID, book title, book author ID, and uh data. We have five rows here as you can see. 1 2 3 4 5. Now let's check since we have foreign key with ondee cascade clause on child table books. Therefore it means that if we delete a row from a parent table authors then the corresponding value to that row in the child table will get deleted automatically. That is the purpose of ondee casket clause. So let's delete first row of parent table authors where we have author ID 1. Let me show you. Here is our author table and we are going to delete this table uh sorry row one where we have author ID one and author name Bob. Let's adjust this SQL developer. So let's delete this row from parent table and see what happen with the corresponding row in child table. So let's have our child table here. This is our child table. And now let's delete row one from author table. For that just write delete. Okay. Execute. One row deleted. Okay. First row is deleted from author table. Let's refresh the data of child table. Here you can see we have five rows. Now let's refresh. There we go. Two rows, row one and row five are deleted automatically. That's what ondee cascade clause does. Hope you learned something. You can visit my website to read more about ondelete casket here. You can find the link in the description below. Help me spreading the word by sharing my video with your friends on your social media. That's it for today. We will see you soon with another SQL tutorial. Till then, take care. This is Manish. Oh yeah, one more thing. Don't forget to like and subscribe. Thanks.