There's one more thing I want to cover that has to do with foreign keys and deletion.
So I have these two columns or two tables where one has a foreign key customer ID, right for each order
references a customer ID no surprise.
Hopefully it probably sick of this.
But what is important to note is that we could have a customer without an order.
That's totally valid.
Now here's something to consider.
What should happen when we delete a customer from our database if we delete Boy George.
Well, Boy George has orders over here.
What do we want to happen?
And there's different options.
Different solutions.
But let me show you what happens right now.
If I try and delete Boy George, just as a reminder, he has two orders in the database I'll delete
from customers.
Where?
And then let's just do the last name equals George.
I get an error, it says cannot delete or update that parent row.
A foreign key constraint fails.
Its saying by deleting that all of a sudden you you make the foreign key constraint fail because the
foreign key constraint says that every customer ID inside of orders has to be a real customer ID, But
if we suddenly delete this row, well, this is no longer a real valid customer ID that exists in the
database.
So there are different options for how you handle that.
One option would be to update the customer ID on delete to be something like no right or unknown or
deleted.
Another option, though, is to delete all of the orders placed by Boy George when we delete Boy George.
And that's how things depending on the website you're on.
If I delete my Facebook account, for example, all of my comments are gone.
I think they do save them.
You can.
They're not truly gone.
But from somebody else's perspective, it looks like my comments are gone.
Whereas on Reddit I can delete my Reddit account and I'm pretty sure comments are still left, but it
doesn't show that they were my comments.
It just says deleted for the username.
So it depends on the situation.
But I want to show you how we can automatically have any orders delete when the corresponding customer
who made the order is deleted.
So we're going to have to go back and update our schema, update the table, or probably just start
over, although you could actually modify using alter table.
But the approach that we'll take is to add something to our schema called on delete cascade.
So let me go take our table definitions one more time right here where we made our two tables.
Just going to do it again down here.
When we make the orders table, we set up the form key constraint references customers ID I'm going
to say on delete.
Cascade.
In this says this foreign key that references the customers ID if that customer with the ad ID is deleted
from the customer's table, delete every row from orders that has that as its customer ID.
So I'm going to have to drop my table, do this again.
And that's actually another situation with the way my schema is set up right now if I try and drop customers.
I also get an error because it says, Hey, what?
You have the orders table right now that has all these foreign keys that suddenly would be incorrect.
So I'm going to just drop table orders first and then I can drop customers.
Okay, then I'll remake them using this new on delete cascade, and then I'll just re-insert the same
data from before.
Okay, now I can delete.
Well, let me just show you.
If we select star from orders, we know Boy George has ID of one.
So these are his orders right there.
If I delete boy George.
Previously I couldn't, because where I would have had to delete the orders first, and then I could
delete him as a customer.
But if I do a delete from customers where last name equals George.
No problem.
He's gone.
Select star from customers.
We have George Michael.
Don't let that confuse you.
Boy George is gone.
And if I go back to look at my orders now, we only have three of them.
The corresponding orders associated with George Boy George, Customer ID of one.
Those were deleted automatically.
So now if I delete George Michael as a customer.
So we're last name is Michael.
And we look at orders, we're down to one order.
Automatically that deletion cascaded to any row that had that customer ID that was just removed.
So you don't always want this.
Like I said, there's different options.
You may not want to have that cascading behavior if it makes no sense to keep an order around after
the corresponding customer is gone, we will delete those orders and we set that up using on delete
cascade.
When we create or tell my SQL about the foreign key constraint.