All right.
So we discussed our game plan for implementing the relationship, the one to 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, an email.
And these three, the last three are all of our chars.
An ID is an INT.
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 then just copy the copy and paste the data in because there's no reason you need to
spend time typing Blue Steel and all these email addresses over and over.
So create table customers.
And let's just start by defining what we need.
So we have an ID, we have first name, we have last name and we have email.
And as we said, these are all far chars and we'll just do 100.
And I'll copy that and paste it.
I had my semi-colon, and so we're almost there.
We just need to figure out ID and this should be review we have INT and we could leave it at that,
but it's best to add auto increment and primary key and that's because well, 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, we are 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 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 at the same ID, that would be problematic.
So we're going to make it unique with auto or with primary key and auto increment.
Now primary key on its own enforces the fact that it's unique, but we would have to manually enter
it in.
There 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, okay, this user has ID of two and a 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 auto increment, so we'll
do that.
So that gives us customers, let's make sure we add our comma there.
And then next up, we have orders and order has an ID as well.
So that will also be a primary key and we have order, 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
order 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 chart.
So we don't want to do that.
So we'll call it order date and then we have amount OC.
So Order Date is just going to be a regular date.
We won't worry with date time, even though I prefer date time just to make it simpler 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're just going to eliminate that.
So date and then this will be a decimal and for our purposes let's say eight comma two.
So we're eight is the total number of digits and two is the number after the decimal.
So we'll start with that decimal eight comma two order date is just a simple date and then ID is an
int and we'll do the same auto increment primary key.
But then that brings us to the last thing which is different.
We haven't seen this before 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.
So let me add my semicolon 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, but it's been a while.
How do I check the name of the database that I'm currently in?
It's 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 customer's table and see if it works.
And then our orders table.
Okay, perfect.
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,
it's adding how many users?
Five users.
I added one who wasn't in our database already or who wasn't on the the table here, which is Betty
Davis.
So we have Boy George, George Michael, David Bowie, Blue Steel and Betty 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 trickier 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 one, one, two, two and
five, which is fine.
That looks good to us because we also have users who go up until five, but there is 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 33.66 or 67.
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 customer ID 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 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 as 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 or the column we're 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, 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.
Oops, I lost it.
Here we are.
So it seems like a lot, but all that we're saying is, hey, there's this column that we already defined.
Customer ID is an int.
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 to the customer's table and in particular the ID field.
One small note I'll add your keys don't always have to be IDs that they're almost always IDs, but you
can get away with other things.
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 auto increment them and it's 99.9% of the time what you'll use.
So here we go again.
Just to go over the steps, you define first customer ID and then just to clarify, you can call this
customer ID, you can call this whatever you want.
I gave it just regular ID and gave this customer underscore ID, 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 we already have an ID here.
So we're taking this making a 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 a drop table and we can do it both at once.
So customer's orders.
Okay.
And now how do we see our tables?
It's a quick review as well.
Show tables, nothing.
So I'm just going to paste this in again.
We got 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 select 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 what 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.
Let's do June 6th and our price will be 33.67, but our ID is going to be 98, 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 five.
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 it's saying I can't do that because the constraint you had said this was 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 orders, 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.
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 associated, but now how do I do things with them?
How do I figure out who ordered?
What 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 email?
So that's coming up in the next video.
And we're going to dive into a really important topic, which is joints.
So that's coming up.