WEBVTT 00:00.110 --> 00:00.610 All right. 00:00.630 --> 00:04.050 So we discussed our game plan for implementing the relationship. 00:04.050 --> 00:07.680 The one too many association between customers and orders. 00:07.680 --> 00:09.110 Now let's get to it. 00:09.120 --> 00:12.680 So we'll start off with customers which is a little bit easier. 00:12.990 --> 00:16.190 So we're going to have an ID a first name a last name and email. 00:16.470 --> 00:21.360 And these three the last three are all of our chars and ID isn't it. 00:21.420 --> 00:24.420 So let's start off with that and I'll let you know. 00:24.600 --> 00:29.370 I have basically our data already written so that we don't have to spend time typing it out. 00:29.430 --> 00:33.840 You'll be able to copy and paste it but I do think it's good if you go through actually typing the schema 00:33.840 --> 00:39.540 itself and just copy the copy and paste the data in because there's no reason you need to spend time 00:40.080 --> 00:44.360 typing Plus deal and all these email addresses over and over. 00:44.530 --> 00:54.980 You get to create table customers and let's just start by defining what we need so we have an ID we 00:54.980 --> 01:02.680 have first name last name and we have email and as we said these are all our chars. 01:02.800 --> 01:10.490 And we'll just do one hundred and I'll copy that and paste it had my semi-colon. 01:11.120 --> 01:12.280 And so we're almost there. 01:12.410 --> 01:14.010 We just need to figure out ID. 01:14.030 --> 01:18.610 And this should we review it and we could leave it at that. 01:19.040 --> 01:23.790 But it's best to add autoincrement and primary key. 01:24.740 --> 01:26.600 And that's because a couple of things. 01:26.600 --> 01:32.690 One it's a good habit to get into at the very least it can be useful for sorting things but more importantly 01:32.990 --> 01:38.300 where you're going to reference this ID customer's ID in another table. 01:38.490 --> 01:41.250 And so we want to make sure that it's unique like we discussed. 01:41.540 --> 01:44.930 We don't want to have you know Customer ID too. 01:45.470 --> 01:50.510 We don't want any ambiguity as to who or which customer that's referring to if we had two different 01:51.440 --> 01:53.730 customers with the same ID that would be problematic. 01:53.750 --> 02:00.650 So we're going to make it unique with auto or with primary key and autoincrement primary key on its 02:00.650 --> 02:02.930 own inforced is the fact that it's unique. 02:03.290 --> 02:05.470 But we would have to manually enter it in. 02:05.480 --> 02:14.060 It should be review but I would have to actually say something like insert into customers and then what 02:14.060 --> 02:21.140 would I do here let's say id and I'll just do first name I would have to manually go through and say 02:21.170 --> 02:27.110 OK this user has ID of two and the first name of Tom and then the next one I would have to make sure 02:27.110 --> 02:33.050 it was unique and do three or I could do 20 or 50 as long as it was unique but I don't have to care 02:33.050 --> 02:34.000 about that ID. 02:34.280 --> 02:36.650 If I put autoincrement. 02:36.710 --> 02:38.040 So we'll do that. 02:38.520 --> 02:38.960 OK. 02:39.020 --> 02:45.680 So that gives us customers to make sure we had our camera there and then next up we have orders and 02:45.710 --> 02:47.520 Order has an idea as well. 02:47.540 --> 02:51.680 So that will also be a primary key and we have ordered date and amount. 02:51.710 --> 03:02.110 So let's start with those three create table orders and the first thing we have is ID. 03:02.630 --> 03:09.320 And then we had ordered date and you might be inclined just to call it date but that can be problematic 03:09.350 --> 03:14.970 because that is an actual data type it would be like naming a column int or var char. 03:15.170 --> 03:16.090 So we don't want to do that. 03:16.160 --> 03:21.780 So we'll call it order date and then we have a mount. 03:22.430 --> 03:25.820 So order date it's just going to be a regular date. 03:25.970 --> 03:31.580 We won't worry with date time even though I prefer date time just to make it simple for us we won't 03:31.580 --> 03:33.370 have to deal with the time component. 03:33.410 --> 03:34.670 So that's not what this is. 03:34.670 --> 03:39.600 This exercise is focused on so we can eliminate that so date and then this will be a decimal. 03:39.860 --> 03:44.000 And for our purposes let's say 8 comet 2. 03:44.000 --> 03:48.340 So we're 8 is the total number of digits and 2 is the number after the decimal. 03:49.040 --> 03:53.640 So we'll start with that decimal 8 coming to order date. 03:53.660 --> 04:01.840 It's just a simple date and then Id isn't the same auto increment primary key. 04:02.360 --> 04:08.950 But then that brings us to the last thing which is different and we haven't seen this before it and 04:08.960 --> 04:12.020 all we're going to do is write customer ID. 04:12.770 --> 04:17.570 That is the name of the column what data type is it. 04:17.960 --> 04:20.570 Well it's just going to be a number it's an integer. 04:20.570 --> 04:24.470 So we could just leave it at that if we wanted to. 04:25.430 --> 04:27.590 And I'll show you what that looks like to start. 04:27.710 --> 04:30.310 So I will go ahead and leave it at this. 04:30.440 --> 04:35.630 I'm not recommending that you actually run this just yet because we are going to change this and I'll 04:35.630 --> 04:37.340 show you why in just a second. 04:37.340 --> 04:37.790 OK. 04:38.000 --> 04:43.460 So we had my semi colon in here and then let's make sure we don't have any typos. 04:43.460 --> 04:46.730 The first thing I'll show you is that I'm in a new database. 04:46.730 --> 04:47.760 This is a bit of review. 04:47.810 --> 04:48.630 It's been a while. 04:48.680 --> 04:50.230 How do I check the name of the database. 04:50.240 --> 04:54.760 I'm currently in it to select database. 04:55.030 --> 04:56.520 So I made a new one. 04:56.540 --> 04:57.920 It doesn't matter. 04:57.920 --> 04:59.840 You could do it inside the bookshop. 04:59.870 --> 05:05.120 I just wanted to have a clean thing we have no tables yet so I'm going to make our customers table and 05:05.120 --> 05:06.430 see if it works. 05:06.470 --> 05:08.830 And then our orders table. 05:09.430 --> 05:10.530 Okay perfect. 05:10.970 --> 05:19.190 So then I have all this data down here that I'm not going to type by hand but if you take a look it's 05:19.190 --> 05:21.770 adding How many users five users. 05:22.060 --> 05:28.070 I added one who wasn't in our database already or who wasn't on the table here which is Bette Davis 05:28.520 --> 05:31.780 so Boy George George Michael David Bowie blue steel and Bette Davis. 05:31.790 --> 05:36.660 So that's just going to insert into customers make sure that works. 05:36.730 --> 05:40.310 Let's do a select star from customers. 05:40.310 --> 05:41.530 Perfect. 05:41.620 --> 05:46.030 And then this is a tricky part where I'm actually inserting the orders. 05:46.460 --> 05:52.700 And if you notice all that I'm doing is the date of the order the price and then Customer ID at the 05:52.700 --> 05:53.770 end. 05:53.780 --> 06:00.720 And right now there is no explicit association between customer ID here and ID here. 06:00.950 --> 06:05.060 All that I've said is this is a number so it's up to me to put whatever I want in there. 06:05.300 --> 06:06.420 So let me show you. 06:06.920 --> 06:12.140 I'm just going to do that now by copying this and pasting it in. 06:12.420 --> 06:21.250 And now if I do a select star from orders you can see we have customer IDs 1 1 2 2 and 5 which is fine. 06:21.280 --> 06:29.080 That looks good to us because we also have users who go up until 5:00 but there's nothing stopping me 06:29.260 --> 06:31.830 from doing something like this. 06:31.900 --> 06:36.650 Let me just copy this first part. 06:36.710 --> 06:43.280 There's nothing stopping me from inserting something with let's say price is thirty three point sixty 06:43.280 --> 06:45.090 six sixty seven. 06:45.100 --> 06:46.310 Why not. 06:46.400 --> 06:50.050 And customer ID is 95 or 98. 06:50.510 --> 06:52.410 There's nothing stopping you from doing this. 06:52.580 --> 06:54.690 And that's not well. 06:54.740 --> 06:56.560 I mean we could get by with this right. 06:56.570 --> 07:01.270 We could just know that we're never going to insert something with a cast right that doesn't exist. 07:01.370 --> 07:08.900 But because there is no association between customer ID and this ID we can do whatever we want which 07:08.900 --> 07:09.890 is usually not a good thing. 07:09.890 --> 07:18.500 So if I hit Enter now we have an order with a customer id that doesn't actually exist with a corresponding 07:18.500 --> 07:19.440 customer. 07:19.880 --> 07:23.870 So what what we want to do is actually add in a constraint here. 07:23.900 --> 07:28.750 So just like there's a primary key we also can explicitly say something is a foreign key. 07:29.000 --> 07:31.280 And the easiest way it's a little longer. 07:31.280 --> 07:37.040 It looks like this on a separate line foreign key and then in parentheses we say the name of the field 07:37.040 --> 07:40.920 to the column are referring to which is in the Orders table. 07:41.030 --> 07:45.730 Customer ID and then we have to say what it's referencing. 07:45.740 --> 07:51.070 So it's referencing think of drawing this line up to ID. 07:51.110 --> 07:51.610 OK. 07:51.830 --> 07:54.310 So we have customer id up there. 07:54.740 --> 08:01.220 So we're going to do references and then the name of the table which is customers. 08:01.220 --> 08:08.550 And then in parentheses the name of the field which is ID ups I lost it here. 08:09.010 --> 08:09.330 OK. 08:09.350 --> 08:14.960 So it seems like a lot but all we're saying is hey there's this column that we need to find customer 08:14.970 --> 08:21.170 ID and make that a foreign key and it needs to reference remember foreign key means that it's referencing 08:21.170 --> 08:23.320 something external something outside the table. 08:23.360 --> 08:29.090 We want it to reference the customers table and in particular the ID field. 08:29.090 --> 08:33.510 One small note I'll add your key is don't always have to be IDs. 08:33.510 --> 08:38.350 There are almost always IDs but you can get away with other things you like. 08:38.360 --> 08:39.830 I think we discussed this earlier. 08:39.830 --> 08:43.640 Sometimes you want emails to be unique and you could make that a primary key instead. 08:43.760 --> 08:49.160 But IDs are easiest because they're short they're simple you can autoincrement them and it's ninety 08:49.160 --> 08:52.310 nine point nine percent of the time what you use. 08:52.370 --> 09:00.370 So here we go again just go up the steps you define first customer I.D. and then and just to clarify 09:01.040 --> 09:04.780 you can call this customer I.D. you can call this whatever you want. 09:04.820 --> 09:11.480 I gave just regular I.D. and gave this customer underscore I.D. which is a convention when you have 09:11.480 --> 09:17.930 a foreign key to use the name of the table underscore the name of the column just so that it's clear. 09:17.990 --> 09:19.790 And you know we already have an idea here. 09:20.040 --> 09:20.270 OK. 09:20.270 --> 09:21.200 So we're taking this. 09:21.200 --> 09:25.510 Making a good foreign key referencing this field here. 09:25.510 --> 09:29.990 So to see the effect of that we're actually going to need to drop our tables and start over. 09:30.140 --> 09:39.250 So it's just drop table and we can do both at once so customers orders OK and now how do we see our 09:39.250 --> 09:39.700 tables. 09:39.700 --> 09:44.740 That's a quick review as well show tables nothing. 09:45.180 --> 09:45.500 OK. 09:45.530 --> 09:51.320 So I'm just going to face this again again we had our first table make sure we didn't screw up our syntax. 09:51.340 --> 09:55.740 The second one great only difference is this line here. 09:55.900 --> 10:02.520 Now we're going to insert our customers again and insert our orders again. 10:03.160 --> 10:09.730 And if we do a show like Star from customers you can see nothing changed to a select star from orders 10:11.260 --> 10:12.390 nothing changed. 10:12.430 --> 10:18.390 Great but then now let's try and insert something else like we did earlier. 10:18.560 --> 10:22.230 So I'm just going to copy this line again down below. 10:22.690 --> 10:25.760 And we're going to insert an order. 10:25.780 --> 10:33.640 We can keep the same date that's due June 6th and our price will be thirty three point six seven. 10:33.910 --> 10:39.190 But our idea is going to be ninety eight which doesn't exist. 10:39.550 --> 10:45.820 Our customer ID is 98 which doesn't exist as a corresponding user or customer ID as you can see we only 10:45.820 --> 10:47.040 go up till 5. 10:47.530 --> 10:54.730 So if we try that now we have a problem which we didn't have before. 10:54.950 --> 10:58.970 Cannot add or update a child row a foreign key constraint fails. 10:58.970 --> 11:04.970 And so basically saying I can't do that because the constraint you had said this is a foreign key referencing 11:04.970 --> 11:08.020 customer ID or customers in the ID field. 11:08.060 --> 11:10.450 And there is no customer with ID 98. 11:10.610 --> 11:13.430 So it's now enforcing that which is great. 11:13.490 --> 11:19.680 And if we select all of our borders again you can see it's not in here. 11:19.910 --> 11:21.050 It didn't make it in here. 11:21.470 --> 11:22.740 So we now have this. 11:22.820 --> 11:29.440 Think of it as a bouncer at the door that is checking if there's a corresponding customer which is useful. 11:29.770 --> 11:30.020 OK. 11:30.050 --> 11:31.640 So that's what foreign key does. 11:31.790 --> 11:36.080 And I'll go ahead and end this video there but we haven't really explored our data yet. 11:36.080 --> 11:40.430 So in the next video we're going to start seeing how we can work with our data. 11:40.670 --> 11:43.990 How do we how do we basically connect these two tables. 11:44.000 --> 11:48.150 We have them a sociate ad but now how do I do things with them. 11:48.260 --> 11:50.270 How do I figure out who ordered what. 11:50.280 --> 11:52.990 Like if I look at orders right now I have a customer ID. 11:53.000 --> 11:58.160 But what if I want the name of the person the first name or both first and last name or the e-mail. 11:58.160 --> 11:59.900 So that's coming up in the next video. 11:59.960 --> 12:03.390 And we're going to dive into really important topic which is joint. 12:03.460 --> 12:04.410 So that's coming up.