1
00:00:00,090 --> 00:00:04,920
There's one more thing I want to cover that has to do with foreign keys and deletion.

2
00:00:05,220 --> 00:00:12,450
So I have these two columns or two tables where one has a foreign key customer ID, right for each order

3
00:00:12,450 --> 00:00:15,000
references a customer ID no surprise.

4
00:00:15,000 --> 00:00:17,730
Hopefully it probably sick of this.

5
00:00:17,940 --> 00:00:24,000
But what is important to note is that we could have a customer without an order.

6
00:00:24,210 --> 00:00:25,530
That's totally valid.

7
00:00:25,620 --> 00:00:27,030
Now here's something to consider.

8
00:00:27,030 --> 00:00:33,090
What should happen when we delete a customer from our database if we delete Boy George.

9
00:00:33,270 --> 00:00:36,150
Well, Boy George has orders over here.

10
00:00:36,600 --> 00:00:38,600
What do we want to happen?

11
00:00:38,610 --> 00:00:40,080
And there's different options.

12
00:00:40,080 --> 00:00:41,100
Different solutions.

13
00:00:41,100 --> 00:00:43,260
But let me show you what happens right now.

14
00:00:43,500 --> 00:00:50,310
If I try and delete Boy George, just as a reminder, he has two orders in the database I'll delete

15
00:00:50,310 --> 00:00:52,920
from customers.

16
00:00:53,070 --> 00:00:53,820
Where?

17
00:00:53,820 --> 00:00:57,720
And then let's just do the last name equals George.

18
00:01:00,110 --> 00:01:03,610
I get an error, it says cannot delete or update that parent row.

19
00:01:03,620 --> 00:01:05,530
A foreign key constraint fails.

20
00:01:05,540 --> 00:01:13,820
Its saying by deleting that all of a sudden you you make the foreign key constraint fail because the

21
00:01:13,820 --> 00:01:21,980
foreign key constraint says that every customer ID inside of orders has to be a real customer ID, But

22
00:01:21,980 --> 00:01:28,460
if we suddenly delete this row, well, this is no longer a real valid customer ID that exists in the

23
00:01:28,460 --> 00:01:29,180
database.

24
00:01:29,690 --> 00:01:32,460
So there are different options for how you handle that.

25
00:01:32,480 --> 00:01:41,690
One option would be to update the customer ID on delete to be something like no right or unknown or

26
00:01:41,690 --> 00:01:42,590
deleted.

27
00:01:42,770 --> 00:01:49,850
Another option, though, is to delete all of the orders placed by Boy George when we delete Boy George.

28
00:01:49,970 --> 00:01:53,300
And that's how things depending on the website you're on.

29
00:01:53,540 --> 00:01:58,610
If I delete my Facebook account, for example, all of my comments are gone.

30
00:01:59,300 --> 00:02:00,770
I think they do save them.

31
00:02:00,770 --> 00:02:01,490
You can.

32
00:02:01,880 --> 00:02:02,840
They're not truly gone.

33
00:02:02,840 --> 00:02:07,400
But from somebody else's perspective, it looks like my comments are gone.

34
00:02:07,400 --> 00:02:13,490
Whereas on Reddit I can delete my Reddit account and I'm pretty sure comments are still left, but it

35
00:02:13,490 --> 00:02:15,170
doesn't show that they were my comments.

36
00:02:15,170 --> 00:02:17,510
It just says deleted for the username.

37
00:02:17,630 --> 00:02:19,640
So it depends on the situation.

38
00:02:19,640 --> 00:02:27,140
But I want to show you how we can automatically have any orders delete when the corresponding customer

39
00:02:27,140 --> 00:02:28,970
who made the order is deleted.

40
00:02:29,300 --> 00:02:34,190
So we're going to have to go back and update our schema, update the table, or probably just start

41
00:02:34,190 --> 00:02:37,490
over, although you could actually modify using alter table.

42
00:02:37,490 --> 00:02:45,170
But the approach that we'll take is to add something to our schema called on delete cascade.

43
00:02:45,620 --> 00:02:54,470
So let me go take our table definitions one more time right here where we made our two tables.

44
00:02:54,930 --> 00:02:57,350
Just going to do it again down here.

45
00:02:57,350 --> 00:03:03,140
When we make the orders table, we set up the form key constraint references customers ID I'm going

46
00:03:03,140 --> 00:03:04,760
to say on delete.

47
00:03:05,470 --> 00:03:06,580
Cascade.

48
00:03:07,280 --> 00:03:14,900
In this says this foreign key that references the customers ID if that customer with the ad ID is deleted

49
00:03:14,900 --> 00:03:21,830
from the customer's table, delete every row from orders that has that as its customer ID.

50
00:03:22,280 --> 00:03:25,550
So I'm going to have to drop my table, do this again.

51
00:03:26,570 --> 00:03:32,960
And that's actually another situation with the way my schema is set up right now if I try and drop customers.

52
00:03:35,760 --> 00:03:38,790
I also get an error because it says, Hey, what?

53
00:03:38,790 --> 00:03:45,120
You have the orders table right now that has all these foreign keys that suddenly would be incorrect.

54
00:03:45,120 --> 00:03:50,370
So I'm going to just drop table orders first and then I can drop customers.

55
00:03:52,750 --> 00:03:59,320
Okay, then I'll remake them using this new on delete cascade, and then I'll just re-insert the same

56
00:03:59,320 --> 00:04:00,820
data from before.

57
00:04:03,740 --> 00:04:07,040
Okay, now I can delete.

58
00:04:07,070 --> 00:04:08,330
Well, let me just show you.

59
00:04:08,510 --> 00:04:13,430
If we select star from orders, we know Boy George has ID of one.

60
00:04:13,430 --> 00:04:15,650
So these are his orders right there.

61
00:04:15,680 --> 00:04:17,660
If I delete boy George.

62
00:04:17,660 --> 00:04:22,040
Previously I couldn't, because where I would have had to delete the orders first, and then I could

63
00:04:22,040 --> 00:04:23,470
delete him as a customer.

64
00:04:23,480 --> 00:04:31,130
But if I do a delete from customers where last name equals George.

65
00:04:32,640 --> 00:04:33,670
No problem.

66
00:04:33,690 --> 00:04:34,620
He's gone.

67
00:04:34,620 --> 00:04:36,720
Select star from customers.

68
00:04:37,870 --> 00:04:38,920
We have George Michael.

69
00:04:38,950 --> 00:04:39,970
Don't let that confuse you.

70
00:04:40,000 --> 00:04:41,200
Boy George is gone.

71
00:04:41,410 --> 00:04:46,060
And if I go back to look at my orders now, we only have three of them.

72
00:04:46,300 --> 00:04:51,970
The corresponding orders associated with George Boy George, Customer ID of one.

73
00:04:51,970 --> 00:04:54,220
Those were deleted automatically.

74
00:04:54,580 --> 00:04:57,490
So now if I delete George Michael as a customer.

75
00:04:58,510 --> 00:05:00,940
So we're last name is Michael.

76
00:05:02,400 --> 00:05:05,460
And we look at orders, we're down to one order.

77
00:05:05,700 --> 00:05:13,500
Automatically that deletion cascaded to any row that had that customer ID that was just removed.

78
00:05:13,800 --> 00:05:15,060
So you don't always want this.

79
00:05:15,070 --> 00:05:16,680
Like I said, there's different options.

80
00:05:16,680 --> 00:05:22,920
You may not want to have that cascading behavior if it makes no sense to keep an order around after

81
00:05:22,920 --> 00:05:29,670
the corresponding customer is gone, we will delete those orders and we set that up using on delete

82
00:05:29,670 --> 00:05:30,510
cascade.

83
00:05:30,510 --> 00:05:35,100
When we create or tell my SQL about the foreign key constraint.

