Okay, So let's go ahead and try and define these tables.
So we'll start with customers, which is simple ish.
We have an ID that will be our primary key.
Remember primary key there and it will auto increment.
It's an integer.
Then we'll have first name, last name and email.
They'll all just be simple.
Var chars var cars.
Let's start there.
So I'll create a table called customers and we know that we're going to have an ID in there.
IDI is an int, and I'll just say that it's primary key auto underscore increment.
And then we have first underscore name and last underscore name.
So that will be a var cha.
I don't know.
Let's just say 50 and then last name.
Var cha 50.
And then finally email.
And email is going to be a bar chart 50 as well.
So that's our customers table.
And now we need to work on the orders table, which the first three columns are self self explanatory
or straightforward enough and ID order, date and amount.
So let's do create table orders and we're going to have an ID at the beginning, just like we did with
customers.
So it's an integer primary key auto increment and then we're going to have order date.
Which will make an actual date.
We don't need date time or time or time stamps.
We'll just do date and then amount and amount is going to be a decimal, I would imagine.
Right, with up to two decimal points and let's do it.
I don't know.
Let's do like eight comma two.
So that part's simple enough.
That should all be review.
But now we need to add a column in for the foreign key.
So this column, we can call it customer ID.
I mean, we definitely can't call it ID because we already have ID.
And also that would be confusing.
We need to make it clear that this column belongs to or at least is associated with the customer's table,
doesn't belong to it.
I shouldn't have said that, but it is associated with data in an ID from the customer's table, so
we can just do customer ID as a column.
And what type should it be?
Well, it's an integer over here, so it should be an integer over here.
If we're going to be storing one or two, which are the IDs from the customer table, then of course
it should be the same integer format over here.
Now I'm going to show you a couple of versions of this.
This is the naive version for now.
This technically works.
You don't have to run this if you don't want to, but I'm going to create both of these tables.
Let me go to my my SQL shell.
I'm going to create a database first, create database.
Just call it shop and we'll use shop.
Then I'll paste those two in.
We should now have two tables.
Show tables.
Great.
So I have some pre written insert into statements and I'd like if you want to follow along, you should
copy these so you'll have them following this video.
Download the code or view the code, copy and paste these lines so that you have the same data as me.
And of course it would be great to have some crazy complicated data set.
But in order for us to understand the concepts, it really is beneficial to start small.
So we know there's five different users or customers and there's five different orders, you'll quickly
understand the data set.
We'll be able to tell for doing things correctly or not, just based on having memorized what is in
here.
So we're going to have five different customers Boy, George, George Michael, David Bowie, Blue Steele,
Bette Davis.
And then we have five different orders.
So here's one.
Here's the other.
Both of these are supposed to be placed by whoever has user ID one or customer ID one, which will be
Boy George.
Let's just go ahead and run this and you'll see what happens.
Nothing special happens, but we have our data inserted.
I'll select star from customers.
Great.
This is the easy table.
There's no foreign key involved here.
And then I'll select star from orders.
And there we are.
We have our five orders, each with a customer ID, which is supposed to be referencing an actual customer's
ID.
But the thing is, it doesn't have to write.
I could easily insert into orders a new row.
I'll just do it right now.
Insert into orders a row that has an order date and an amount and a customer ID where the order date.
Let's say it's this year 2022, 11, 11 and.
Make sure I have the right format.
Their amount will be, I don't know, 50.68, and then the customer ID is going to be 975.
But there is definitely no customer with that ID, which would be a problem.
We probably don't want that to happen, but nothing is stopping it except for my stupid syntax error.
What did I mess up here?
Insert into orders.
There's an s there.
That was bad timing, right?
When I said nothing is stopping us, we actually did get stopped.
So anyway, now nothing is stopping us from inserting a completely fictitious customer ID, and that
might not seem that bad.
So what if there's no ID or customer with that ID?
But then when we start to write queries later on and we want to I mean, we probably want to have a
customer associated with every order, we're going to have a bunch of missing data or data that doesn't
make sense.
We want to enforce a rule if we have a foreign key in the orders column or the orders table called customer
ID that is supposed to be linked to a real customer in the customer's table, why don't we set up a
rule or a constraint that says this has to be a real ID, We're going to do that.
And the way that we do that is by using the foreign key constraint.
So just like we have primary key primary key is relatively simple.
It just says this ID in our case has to be unique.
Now, when we set up a foreign key constraint, it doesn't have to be unique, but it does have to reference
a real ID in the other table.
So I'm going to say customer ID is an integer.
I'm just going to leave that.
But then below I'm going to add my foreign key constraint.
So what we do is we write foreign key.
And then in parentheses, the name of the column in this table that we're trying to add this constraint
to.
So this doesn't do a whole lot just yet.
But we're saying this customer ID is a foreign key.
But then the really important part is we say it's referencing references.
The customer's table, right.
The customer's table, this particular column ID and now no semicolon.
Now, we have cemented this relationship.
We have done essentially with this arrow shows here.
We have told my SQL, this is not just any old integer.
You can't just make 10,000 in here.
You can't put nine, nine, nine as a customer ID unless there is truly an ID in the customer's table
of 999.
So whatever value we have in here has to be a valid existing customer.
So let's test it out.
Foreign key customer ID, which is one more time.
That's what this column is called, is referencing the customer's table ID column.
So I'm going to drop my two existing tables that I made, although I really don't have to drop customers,
but I am going to do it just to start fresh so you could copy and paste this yourself if you want to
run it.
Let's just drop the table customers and I need a semicolon drop table orders and then I will create
these two tables again and then I'll insert our data.
This is perfectly valid data, right?
We don't have to worry about this.
I've written this data so that it works.
We have ID of one, two and five.
Well, this should have ID of one, two, three, four, five.
So all of these should be real IDs.
But now if I try and insert, let's just do a select star from order some more time.
If I try and insert into orders, we'll do order date once again.
Amount.
Customer ID.
I'll insert something again that doesn't exist.
So order.
Date 2022 1111 Amount 78.99 And then customer ID I'll insert 54.
But there is no customer with that ID and I hit enter.
We get an error.
It doesn't work.
Says cannot add or update a child row.
A foreign key constraint fails.
In other words, I can't do that.
That customer ID you gave me is not a real customer ID so that is our first and most important step
in setting up this relationship.
Not only do we have to tables where we've added a relationship that we as humans and programmers have
to keep in mind, we have cemented that relationship in my SQL in writing, we've said this customer
ID column, every value has to be a real ID from the real customers table, a foreign table.
A key from that table will be stored in orders.
Okay, so we've set that up.
Simple enough.
I guess I should show it will work if I use an ID that is a valid users ID.
So if we select star from I said users, but customers rather.
If we take David Bowie, who doesn't have any orders right now.
One one, two, two five.
Right.
There's no one, no orders with the customer ID of three.
If I instead said insert with the customer ID of three, it works.
So that's just proof.
It's not going to reject every single ID, it only rejects the ones that don't actually exist in our
customers table.
Okay, so the next thing and the real meat of this section is understanding now that we have a relationship
setup, this one to many relationship where every customer can have many orders, but each order belongs
to one customer.
How do we do some basic operations?
How do we combine the two?
How do I look at this row and write a query that tells me who ordered this?
Or what about finding the total amount spent by each user?
How do we work with these separate tables and join them together?
That's coming up.