All right.
So we discussed our game plan for implementing the relationship.
The one too many association between customers and orders.
Now let's get to it.
So we'll start off with customers which is a little bit easier.
So we're going to have an ID a first name a last name and email.
And these three the last three are all of our chars and ID isn't it.
So let's start off with that and I'll let you know.
I have basically our data already written so that we don't have to spend time typing it out.
You'll be able to copy and paste it but I do think it's good if you go through actually typing the schema
itself and just copy the copy and paste the data in because there's no reason you need to spend time
typing Plus deal and all these email addresses over and over.
You get to create table customers and let's just start by defining what we need so we have an ID we
have first name last name and we have email and as we said these are all our chars.
And we'll just do one hundred and I'll copy that and paste it had my semi-colon.
And so we're almost there.
We just need to figure out ID.
And this should we review it and we could leave it at that.
But it's best to add autoincrement and primary key.
And that's because a couple of things.
One it's a good habit to get into at the very least it can be useful for sorting things but more importantly
where you're going to reference this ID customer's ID in another table.
And so we want to make sure that it's unique like we discussed.
We don't want to have you know Customer ID too.
We don't want any ambiguity as to who or which customer that's referring to if we had two different
customers with the same ID that would be problematic.
So we're going to make it unique with auto or with primary key and autoincrement primary key on its
own inforced is the fact that it's unique.
But we would have to manually enter it in.
It should be review but I would have to actually say something like insert into customers and then what
would I do here let's say id and I'll just do first name I would have to manually go through and say
OK this user has ID of two and the first name of Tom and then the next one I would have to make sure
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
about that ID.
If I put autoincrement.
So we'll do that.
OK.
So that gives us customers to make sure we had our camera there and then next up we have orders and
Order has an idea as well.
So that will also be a primary key and we have ordered date and amount.
So let's start with those three create table orders and the first thing we have is ID.
And then we had ordered date and you might be inclined just to call it date but that can be problematic
because that is an actual data type it would be like naming a column int or var char.
So we don't want to do that.
So we'll call it order date and then we have a mount.
So order date it's just going to be a regular date.
We won't worry with date time even though I prefer date time just to make it simple for us we won't
have to deal with the time component.
So that's not what this is.
This exercise is focused on so we can eliminate that so date and then this will be a decimal.
And for our purposes let's say 8 comet 2.
So we're 8 is the total number of digits and 2 is the number after the decimal.
So we'll start with that decimal 8 coming to order date.
It's just a simple date and then Id isn't the same auto increment primary key.
But then that brings us to the last thing which is different and we haven't seen this before it and
all we're going to do is write customer ID.
That is the name of the column what data type is it.
Well it's just going to be a number it's an integer.
So we could just leave it at that if we wanted to.
And I'll show you what that looks like to start.
So I will go ahead and leave it at this.
I'm not recommending that you actually run this just yet because we are going to change this and I'll
show you why in just a second.
OK.
So we had my semi colon in here and then let's make sure we don't have any typos.
The first thing I'll show you is that I'm in a new database.
This is a bit of review.
It's been a while.
How do I check the name of the database.
I'm currently in it to select database.
So I made a new one.
It doesn't matter.
You could do it inside the bookshop.
I just wanted to have a clean thing we have no tables yet so I'm going to make our customers table and
see if it works.
And then our orders table.
Okay perfect.
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
adding How many users five users.
I added one who wasn't in our database already or who wasn't on the table here which is Bette Davis
so Boy George George Michael David Bowie blue steel and Bette Davis.
So that's just going to insert into customers make sure that works.
Let's do a select star from customers.
Perfect.
And then this is a tricky part where I'm actually inserting the orders.
And if you notice all that I'm doing is the date of the order the price and then Customer ID at the
end.
And right now there is no explicit association between customer ID here and ID here.
All that I've said is this is a number so it's up to me to put whatever I want in there.
So let me show you.
I'm just going to do that now by copying this and pasting it in.
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.
That looks good to us because we also have users who go up until 5:00 but there's nothing stopping me
from doing something like this.
Let me just copy this first part.
There's nothing stopping me from inserting something with let's say price is thirty three point sixty
six sixty seven.
Why not.
And customer ID is 95 or 98.
There's nothing stopping you from doing this.
And that's not well.
I mean we could get by with this right.
We could just know that we're never going to insert something with a cast right that doesn't exist.
But because there is no association between customer ID and this ID we can do whatever we want which
is usually not a good thing.
So if I hit Enter now we have an order with a customer id that doesn't actually exist with a corresponding
customer.
So what what we want to do is actually add in a constraint here.
So just like there's a primary key we also can explicitly say something is a foreign key.
And the easiest way it's a little longer.
It looks like this on a separate line foreign key and then in parentheses we say the name of the field
to the column are referring to which is in the Orders table.
Customer ID and then we have to say what it's referencing.
So it's referencing think of drawing this line up to ID.
OK.
So we have customer id up there.
So we're going to do references and then the name of the table which is customers.
And then in parentheses the name of the field which is ID ups I lost it here.
OK.
So it seems like a lot but all we're saying is hey there's this column that we need to find customer
ID and make that a foreign key and it needs to reference remember foreign key means that it's referencing
something external something outside the table.
We want it to reference the customers table and in particular the ID field.
One small note I'll add your key is don't always have to be IDs.
There are almost always IDs but you can get away with other things you like.
I think we discussed this earlier.
Sometimes you want emails to be unique and you could make that a primary key instead.
But IDs are easiest because they're short they're simple you can autoincrement them and it's ninety
nine point nine percent of the time what you use.
So here we go again just go up the steps you define first customer I.D. and then and just to clarify
you can call this customer I.D. you can call this whatever you want.
I gave just regular I.D. and gave this customer underscore I.D. which is a convention when you have
a foreign key to use the name of the table underscore the name of the column just so that it's clear.
And you know we already have an idea here.
OK.
So we're taking this.
Making a good foreign key referencing this field here.
So to see the effect of that we're actually going to need to drop our tables and start over.
So it's just drop table and we can do both at once so customers orders OK and now how do we see our
tables.
That's a quick review as well show tables nothing.
OK.
So I'm just going to face this again again we had our first table make sure we didn't screw up our syntax.
The second one great only difference is this line here.
Now we're going to insert our customers again and insert our orders again.
And if we do a show like Star from customers you can see nothing changed to a select star from orders
nothing changed.
Great but then now let's try and insert something else like we did earlier.
So I'm just going to copy this line again down below.
And we're going to insert an order.
We can keep the same date that's due June 6th and our price will be thirty three point six seven.
But our idea is going to be ninety eight which doesn't exist.
Our customer ID is 98 which doesn't exist as a corresponding user or customer ID as you can see we only
go up till 5.
So if we try that now we have a problem which we didn't have before.
Cannot add or update a child row a foreign key constraint fails.
And so basically saying I can't do that because the constraint you had said this is a foreign key referencing
customer ID or customers in the ID field.
And there is no customer with ID 98.
So it's now enforcing that which is great.
And if we select all of our borders again you can see it's not in here.
It didn't make it in here.
So we now have this.
Think of it as a bouncer at the door that is checking if there's a corresponding customer which is useful.
OK.
So that's what foreign key does.
And I'll go ahead and end this video there but we haven't really explored our data yet.
So in the next video we're going to start seeing how we can work with our data.
How do we how do we basically connect these two tables.
We have them a sociate ad but now how do I do things with them.
How do I figure out who ordered what.
Like if I look at orders right now I have a customer ID.
But what if I want the name of the person the first name or both first and last name or the e-mail.
So that's coming up in the next video.
And we're going to dive into really important topic which is joint.
So that's coming up.