1 00:00:00,150 --> 00:00:00,630 All right. 2 00:00:00,630 --> 00:00:06,270 So we discussed our game plan for implementing the relationship, the one to many association between 3 00:00:06,270 --> 00:00:07,510 customers and orders. 4 00:00:07,530 --> 00:00:08,900 Now let's get to it. 5 00:00:08,910 --> 00:00:12,630 So we'll start off with customers, which is a little bit easier. 6 00:00:12,780 --> 00:00:16,260 So we're going to have an ID, a first name, a last name, an email. 7 00:00:16,260 --> 00:00:19,520 And these three, the last three are all of our chars. 8 00:00:19,530 --> 00:00:21,150 An ID is an INT. 9 00:00:21,210 --> 00:00:27,480 So let's start off with that and I'll let you know I have basically our data already written so that 10 00:00:27,480 --> 00:00:29,340 we don't have to spend time typing it out. 11 00:00:29,340 --> 00:00:33,450 You'll be able to copy and paste it, but I do think it's good if you go through actually typing the 12 00:00:33,450 --> 00:00:38,880 schema itself and then just copy the copy and paste the data in because there's no reason you need to 13 00:00:38,880 --> 00:00:44,100 spend time typing Blue Steel and all these email addresses over and over. 14 00:00:45,060 --> 00:00:48,690 So create table customers. 15 00:00:50,800 --> 00:00:53,110 And let's just start by defining what we need. 16 00:00:53,110 --> 00:00:59,140 So we have an ID, we have first name, we have last name and we have email. 17 00:01:00,100 --> 00:01:04,150 And as we said, these are all far chars and we'll just do 100. 18 00:01:04,510 --> 00:01:07,390 And I'll copy that and paste it. 19 00:01:08,580 --> 00:01:12,260 I had my semi-colon, and so we're almost there. 20 00:01:12,270 --> 00:01:18,570 We just need to figure out ID and this should be review we have INT and we could leave it at that, 21 00:01:18,900 --> 00:01:26,610 but it's best to add auto increment and primary key and that's because well, a couple of things. 22 00:01:26,610 --> 00:01:28,790 One, it's a good habit to get into. 23 00:01:28,800 --> 00:01:31,140 At the very least, it can be useful for sorting things. 24 00:01:31,140 --> 00:01:38,310 But more importantly, we are going to reference this ID customer's ID in another table. 25 00:01:38,340 --> 00:01:40,170 And so we want to make sure that it's unique. 26 00:01:40,170 --> 00:01:44,550 Like we discussed, we don't want to have customer ID too. 27 00:01:45,360 --> 00:01:49,320 We don't want any ambiguity as to who or which customer that's referring to. 28 00:01:49,320 --> 00:01:53,670 If we had two different customers at the same ID, that would be problematic. 29 00:01:53,670 --> 00:01:59,130 So we're going to make it unique with auto or with primary key and auto increment. 30 00:01:59,310 --> 00:02:05,040 Now primary key on its own enforces the fact that it's unique, but we would have to manually enter 31 00:02:05,040 --> 00:02:05,430 it in. 32 00:02:05,460 --> 00:02:13,800 There should be review, but I would have to actually say something like insert into customers and then 33 00:02:13,800 --> 00:02:14,880 what would I do here? 34 00:02:14,880 --> 00:02:17,790 Let's say ID and I'll just do first name. 35 00:02:18,720 --> 00:02:24,900 I would have to manually go through and say, okay, this user has ID of two and a first name of Tom, 36 00:02:24,900 --> 00:02:30,180 and then the next one I would have to make sure it was unique and do three, or I could do 20 or 50 37 00:02:30,180 --> 00:02:37,200 as long as it was unique, but I don't have to care about that id if I put auto increment, so we'll 38 00:02:37,200 --> 00:02:37,770 do that. 39 00:02:38,970 --> 00:02:42,120 So that gives us customers, let's make sure we add our comma there. 40 00:02:42,450 --> 00:02:47,520 And then next up, we have orders and order has an ID as well. 41 00:02:47,520 --> 00:02:51,750 So that will also be a primary key and we have order, date and amount. 42 00:02:51,750 --> 00:03:03,750 So let's start with those three create table orders and the first thing we have is ID and then we had 43 00:03:03,750 --> 00:03:09,870 order date and you might be inclined just to call it date, but that can be problematic because that 44 00:03:09,870 --> 00:03:11,460 is an actual data type. 45 00:03:11,460 --> 00:03:14,850 It would be like naming a column int or var chart. 46 00:03:15,030 --> 00:03:16,080 So we don't want to do that. 47 00:03:16,080 --> 00:03:21,950 So we'll call it order date and then we have amount OC. 48 00:03:22,380 --> 00:03:25,800 So Order Date is just going to be a regular date. 49 00:03:25,800 --> 00:03:31,620 We won't worry with date time, even though I prefer date time just to make it simpler for us, we won't 50 00:03:31,620 --> 00:03:33,240 have to deal with the time component. 51 00:03:33,240 --> 00:03:34,650 So that's not what this is. 52 00:03:34,650 --> 00:03:36,120 This exercise is focused on. 53 00:03:36,120 --> 00:03:37,290 So we're just going to eliminate that. 54 00:03:37,290 --> 00:03:44,010 So date and then this will be a decimal and for our purposes let's say eight comma two. 55 00:03:44,010 --> 00:03:47,820 So we're eight is the total number of digits and two is the number after the decimal. 56 00:03:48,930 --> 00:03:56,520 So we'll start with that decimal eight comma two order date is just a simple date and then ID is an 57 00:03:56,520 --> 00:04:01,320 int and we'll do the same auto increment primary key. 58 00:04:02,280 --> 00:04:06,810 But then that brings us to the last thing which is different. 59 00:04:06,810 --> 00:04:11,340 We haven't seen this before and all we're going to do is write customer ID. 60 00:04:12,690 --> 00:04:14,850 That is the name of the column. 61 00:04:16,110 --> 00:04:17,459 What data type is it? 62 00:04:17,820 --> 00:04:19,290 Well, it's just going to be a number. 63 00:04:19,290 --> 00:04:20,190 It's an integer. 64 00:04:20,370 --> 00:04:24,300 So we could just leave it at that if we wanted to. 65 00:04:25,350 --> 00:04:27,600 And I'll show you what that looks like to start. 66 00:04:27,600 --> 00:04:30,300 So I will go ahead and leave it at this. 67 00:04:30,300 --> 00:04:35,700 I'm not recommending that you actually run this just yet because we are going to change this and I'll 68 00:04:35,700 --> 00:04:36,960 show you why in just a second. 69 00:04:37,800 --> 00:04:43,350 So let me add my semicolon in here and then let's make sure we don't have any typos. 70 00:04:43,470 --> 00:04:46,590 The first thing I'll show you is that I'm in a new database. 71 00:04:46,590 --> 00:04:48,600 This is a bit of review, but it's been a while. 72 00:04:48,600 --> 00:04:51,210 How do I check the name of the database that I'm currently in? 73 00:04:52,290 --> 00:04:56,340 It's select database, so I made a new one. 74 00:04:56,460 --> 00:04:57,690 It doesn't matter. 75 00:04:58,050 --> 00:04:59,790 You could do it inside the bookshop. 76 00:04:59,790 --> 00:05:01,080 I just wanted to have a clean thing. 77 00:05:01,080 --> 00:05:06,120 We have no tables yet, so I'm going to make our customer's table and see if it works. 78 00:05:06,240 --> 00:05:07,830 And then our orders table. 79 00:05:09,330 --> 00:05:10,350 Okay, perfect. 80 00:05:10,830 --> 00:05:18,600 So then I have all of this data down here that I'm not going to type by hand, but if we take a look, 81 00:05:18,750 --> 00:05:20,610 it's adding how many users? 82 00:05:20,610 --> 00:05:21,750 Five users. 83 00:05:22,050 --> 00:05:27,720 I added one who wasn't in our database already or who wasn't on the the table here, which is Betty 84 00:05:27,720 --> 00:05:28,290 Davis. 85 00:05:28,410 --> 00:05:31,620 So we have Boy George, George Michael, David Bowie, Blue Steel and Betty Davis. 86 00:05:31,620 --> 00:05:34,110 So that's just going to insert into customers. 87 00:05:35,280 --> 00:05:36,450 Make sure that works. 88 00:05:36,630 --> 00:05:39,900 Let's do a select star from customers. 89 00:05:40,230 --> 00:05:41,010 Perfect. 90 00:05:41,430 --> 00:05:46,050 And then this is a trickier part where I'm actually inserting the orders. 91 00:05:46,230 --> 00:05:52,590 And if you notice, all that I'm doing is the date of the order, the price, and then customer ID at 92 00:05:52,590 --> 00:05:53,190 the end. 93 00:05:53,580 --> 00:06:00,810 And right now there is no explicit association between customer ID here and ID here. 94 00:06:00,810 --> 00:06:02,610 All that I've said is this is a number. 95 00:06:02,610 --> 00:06:05,100 So it's up to me to put whatever I want in there. 96 00:06:05,100 --> 00:06:06,360 So let me show you. 97 00:06:06,870 --> 00:06:08,190 I'm just going to do that now by. 98 00:06:08,300 --> 00:06:12,050 Copying this and pasting it in. 99 00:06:12,380 --> 00:06:19,370 And now if I do a select star from orders, you can see we have customer IDs one, one, two, two and 100 00:06:19,370 --> 00:06:21,260 five, which is fine. 101 00:06:21,260 --> 00:06:28,880 That looks good to us because we also have users who go up until five, but there is nothing stopping 102 00:06:28,880 --> 00:06:30,830 me from doing something like this. 103 00:06:31,850 --> 00:06:34,040 Let me just copy this first part. 104 00:06:36,730 --> 00:06:40,670 There's nothing stopping me from inserting something with. 105 00:06:40,690 --> 00:06:45,160 Let's say price is 33.66 or 67. 106 00:06:45,160 --> 00:06:45,700 Why not? 107 00:06:46,300 --> 00:06:49,690 And customer ID is 95 or 98. 108 00:06:50,410 --> 00:06:52,370 There's nothing stopping you from doing this. 109 00:06:52,390 --> 00:06:54,370 And that's not. 110 00:06:54,370 --> 00:06:56,470 Well, I mean, we could get by with this, right? 111 00:06:56,470 --> 00:07:01,210 We could just know that we're never going to insert something with a customer ID that doesn't exist. 112 00:07:01,210 --> 00:07:08,920 But because there is no association between customer ID and this ID, we can do whatever we want, which 113 00:07:08,920 --> 00:07:09,910 is usually not a good thing. 114 00:07:09,910 --> 00:07:18,550 So if I hit enter now we have an order with a customer ID that doesn't actually exist with a corresponding 115 00:07:18,550 --> 00:07:19,210 customer. 116 00:07:19,720 --> 00:07:23,800 So what we want to do is actually add in a constraint here. 117 00:07:23,800 --> 00:07:28,780 So just like there's a primary key, we also can explicitly say something as a foreign key. 118 00:07:28,780 --> 00:07:31,240 And the easiest way it's a little longer. 119 00:07:31,240 --> 00:07:33,970 It looks like this on a separate line, foreign key. 120 00:07:33,970 --> 00:07:39,760 And then in parentheses we say the name of the field or the column we're referring to, which is in 121 00:07:39,760 --> 00:07:42,160 the orders table, customer ID. 122 00:07:43,300 --> 00:07:45,610 And then we have to say what it's referencing. 123 00:07:45,610 --> 00:07:54,220 So it's referencing, think of drawing this line up to ID, so we have customer ID up there. 124 00:07:54,580 --> 00:08:02,500 So we're going to do references and then the name of the table, which is customers and then in parentheses 125 00:08:02,740 --> 00:08:05,410 the name of the field which is ID. 126 00:08:06,040 --> 00:08:07,390 Oops, I lost it. 127 00:08:07,630 --> 00:08:08,260 Here we are. 128 00:08:09,370 --> 00:08:14,560 So it seems like a lot, but all that we're saying is, hey, there's this column that we already defined. 129 00:08:14,560 --> 00:08:16,030 Customer ID is an int. 130 00:08:16,120 --> 00:08:19,270 Make that a foreign key and it needs to reference. 131 00:08:19,270 --> 00:08:23,230 Remember, foreign key means that it's referencing something external, something outside the table. 132 00:08:23,230 --> 00:08:28,060 We want it to reference to the customer's table and in particular the ID field. 133 00:08:28,900 --> 00:08:36,010 One small note I'll add your keys don't always have to be IDs that they're almost always IDs, but you 134 00:08:36,010 --> 00:08:37,720 can get away with other things. 135 00:08:38,080 --> 00:08:39,760 Like I think we discussed this earlier. 136 00:08:39,760 --> 00:08:43,659 Sometimes you want emails to be unique and you could make that a primary key instead. 137 00:08:43,659 --> 00:08:46,900 But IDs are easiest because they're short, they're simple. 138 00:08:46,900 --> 00:08:51,640 You can auto increment them and it's 99.9% of the time what you'll use. 139 00:08:52,240 --> 00:08:53,860 So here we go again. 140 00:08:53,860 --> 00:09:02,680 Just to go over the steps, you define first customer ID and then just to clarify, you can call this 141 00:09:02,680 --> 00:09:04,750 customer ID, you can call this whatever you want. 142 00:09:04,750 --> 00:09:10,870 I gave it just regular ID and gave this customer underscore ID, which is a convention. 143 00:09:10,870 --> 00:09:16,390 When you have a foreign key to use the name of the table, underscore the name of the column just so 144 00:09:16,390 --> 00:09:19,720 that it's clear and we already have an ID here. 145 00:09:20,290 --> 00:09:24,220 So we're taking this making a foreign key, referencing this field here. 146 00:09:25,540 --> 00:09:29,980 So to see the effect of that, we're actually going to need to drop our tables and start over. 147 00:09:29,980 --> 00:09:33,880 So it's just a drop table and we can do it both at once. 148 00:09:33,880 --> 00:09:36,190 So customer's orders. 149 00:09:37,030 --> 00:09:37,620 Okay. 150 00:09:37,630 --> 00:09:39,700 And now how do we see our tables? 151 00:09:39,700 --> 00:09:41,230 It's a quick review as well. 152 00:09:41,740 --> 00:09:44,710 Show tables, nothing. 153 00:09:45,520 --> 00:09:47,530 So I'm just going to paste this in again. 154 00:09:48,130 --> 00:09:51,340 We got our first table, make sure we didn't screw up our syntax. 155 00:09:51,400 --> 00:09:53,140 The second one great. 156 00:09:53,500 --> 00:09:55,390 Only difference is this line here. 157 00:09:55,720 --> 00:10:02,440 Now we're going to insert our customers again and insert our orders again. 158 00:10:03,070 --> 00:10:09,970 And if we do a select star from customers, you can see nothing changed to a select star from orders. 159 00:10:11,200 --> 00:10:12,160 Nothing changed. 160 00:10:12,280 --> 00:10:12,910 Great. 161 00:10:13,090 --> 00:10:18,460 But then now let's try and insert something else like what we did earlier. 162 00:10:18,550 --> 00:10:25,750 So I'm just going to copy this line again down below and we're going to insert an order. 163 00:10:25,750 --> 00:10:27,130 We can keep the same date. 164 00:10:27,550 --> 00:10:39,100 Let's do June 6th and our price will be 33.67, but our ID is going to be 98, which doesn't exist. 165 00:10:39,460 --> 00:10:44,740 Our customer ID is 98, which doesn't exist as a corresponding user or customer ID. 166 00:10:44,770 --> 00:10:46,780 As you can see, we only go up till five. 167 00:10:47,410 --> 00:10:49,870 So if we try that now. 168 00:10:51,950 --> 00:10:54,770 We have a problem which we didn't have before. 169 00:10:54,800 --> 00:10:56,500 Cannot add or update a child row. 170 00:10:56,510 --> 00:10:58,520 A foreign key constraint fails. 171 00:10:59,030 --> 00:11:04,100 And so basically it's saying I can't do that because the constraint you had said this was a foreign 172 00:11:04,100 --> 00:11:10,430 key referencing customer ID or customers in the ID field and there is no customer with ID 98. 173 00:11:10,430 --> 00:11:13,400 So it's now enforcing that, which is great. 174 00:11:13,400 --> 00:11:19,520 And if we select all of our orders, again, you can see it's not in here. 175 00:11:19,820 --> 00:11:21,020 It didn't make it in here. 176 00:11:21,320 --> 00:11:27,170 So we now have this think of it as a bouncer at the door that is checking if there's a corresponding 177 00:11:27,170 --> 00:11:29,330 customer, which is useful. 178 00:11:30,050 --> 00:11:33,860 So that's what foreign key does and I'll go ahead and end this video there. 179 00:11:34,220 --> 00:11:36,110 But we haven't really explored our data yet. 180 00:11:36,110 --> 00:11:40,340 So in the next video, we're going to start seeing how we can work with our data. 181 00:11:40,670 --> 00:11:43,970 How do we how do we basically connect these two tables? 182 00:11:43,970 --> 00:11:48,140 We have them associated, but now how do I do things with them? 183 00:11:48,260 --> 00:11:50,060 How do I figure out who ordered? 184 00:11:50,060 --> 00:11:54,980 What if I look at orders right now I have a customer ID, but what if I want the name of the person, 185 00:11:55,070 --> 00:11:57,950 the first name or both, first and last name or the email? 186 00:11:57,950 --> 00:11:59,960 So that's coming up in the next video. 187 00:11:59,960 --> 00:12:03,410 And we're going to dive into a really important topic, which is joints. 188 00:12:03,410 --> 00:12:04,340 So that's coming up.