why hello there everybody today I will be explaining the on delete Clause there's two versions on delete set null and on delete Cascade with on delete set null when a foreign key is deleted we can replace the foreign key with the value null otherwise there's on delete Cascade when a foreign key is deleted delete the entire row here's an example let's take a look at our table transactions select all from transactions in my transactions table the customer ID column is the foreign key what would happen if I were to delete one of these customers let's select all from our customers table I'm going to delete poppy puff she has a customer ID of four delete from customers where customer ID equals 4 if you run into this cannot delete or update a parent row a foreign key constraint fails there's a foreign key that's preventing us from deleting this customer because this customer is used elsewhere in a different table like that transactions table so just temporarily for this demonstration I'm going to set foreign key checks to be zero and that should work our customer with an idea 4 is gone 1 2 3 5 poppy puff is no longer within our customer table before before I forget I'm going to set forign key checks back to one let's take a look at our transactions table underneath this transaction of 105 we're referencing a customer that doesn't exist the customer with an idea 4 is no longer within our customer table we have a couple options whenever we delete a primary key from another table that's being used as a foreign key somewhere else we can either replace that value with null or delete the entire row with Cascade with on delete set null we can replace this value with null much like these two rows or on delete Cascade we can delete the entire row let's begin with on delete set null I'm going to reinsert Poppy puff back into our table of customers and there we are poppy puff is back in the customers table now with on delete set null if we're creating a new table let's say I'm creating my table of transactions after adding the foreign key constraint foreign key our customer ID column references the customer ID column of the customer table I can add this Clause UND delete set no that's if I'm creating a new table but I already do have a transactions table I don't want to update it however we can update an existing table with this Clause I already do have a foreign key constraint I'm just going to drop it real quick alter table transactions drop foreign key then the name of the foreign key mine is FK customer ID and that key is now dropped we are going to add a foreign key constraint to our transactions table with this clause on delete set null alter table the name of the table add constraint you can give your constraint a name I'll give it the same name as before FK customer ID list the foreign key foreign key my foreign key is customer ID references another table which was customers and what is the primary key of that table customer ID then we will add the clause on delete set null if I delete a customer ID from customers the foreign key of that row will be set to null automatically let's execute this let's take a look at our transactions table select all from transactions I am now going to delete customer number four from the customers table delete from customers where customer ID equals 4 yeah check that out with our transaction ID of 105 the customer ID is now null now there's on delete Cascade when a foreign key is deleted we can instead delete the entire row let's go ahead and add poppy puff back to our customer table all right she's back in let's drop the current foreign key constraint of our transactions table alter table transactions drop foreign key FK customer ID or whatever else it's named and that key is now gone we will add the on delete Cascade Clause to a table that already exists alter table transactions add constraint you can come up with the name FK uncore transactions uncore ID list the foreign key foreign key customer ID references the customers table and the primary key of that table is the customer ID then add on delete Cascade let's take a look at our transactions table select all from transactions oh let's update this customer ID real quick I forgot to do that update transactions set customer ID equal to 4 where transaction ID equals 1005 yeah I forgot to add that back now let's do it delete from customers where customer ID equals 4 yeah take a look at that the entire row is now gone we have transactions 13 10004 1006 1005 is missing because it was referencing the customer with an ID of four we set the forign key to delete the entire row when that foreign key is deleted that is on delete Cascade well in conclusion everybody on delete is a clause where if you delete a foreign key you can set that value to be null or Cascade which will delete the entire row which has its uses and well yeah that's the on delete clause in MySQL