welcome back to Model Three sequel programming were in less than five tables. Sub lesson 5.4 Foreign Keys and the previous lesson. We discussed Primary keys and their Purpose. Their purpose. Being simply stated to provide a unique value for each record in that table, and every database table should have one.
this lesson you discuss foreign keys. So what is a foreign key? A foreign key is a column of data
that is a primary key in another table.
what? What that allows you to do is communicate
between data against different tables. For example, we have a person table if, ah, we see a address table and that address table has a column called Person I D. And we see person I D five in that table. We know that belongs to a person with i D. Five in the person table.
Now let's go ahead and create another table and connect it to the person table using a foreign key relationship.
So left click on tables, right click create new table,
and we're for a moment going to pretend that we are, Ah, a dog walker with our own application
and we have a list of people with dogs.
Um, sorry. If you were a cat lover,
we might make a cat table later. But for now, we're just going to dogs. We're gonna save the dog table.
And again, I right clicked on. Ah,
all right, Yeah, right. Clicked on tables and selected. Create new table to start this process,
I'm gonna go ahead and make sure I'm in the columns area. I'm gonna right click
Again. I'm gonna give the I d. A. A data type of big ant unsigned. That means more memory. And don't worry about the negative imagers
we're gonna We're not gonna make it auto increment yet, but we will.
We're gonna create another new column we call it breed
on, and we're gonna leave that with the defaults, and then we're gonna click another new column.
We're gonna call it a name.
So the dog's name, we're gonna leave that the faults there.
And now we're going to create a new column on We're going to call this one
And we're going to say that's a big end. Unsigned.
It's not allowed to be known,
and this one's not allowed to be no leader.
Let's go in and save what we have so far.
We're gonna add a constraint. This is the primary key. Add that primary key. Create new constraint.
The faults to the primary key. Select the idee click Okay,
Now let's go turn on that auto increment. We have to do it in this order because if we try to turn them both on at the same time, we'll get in there.
Well, maybe that's still there. So
let's see here. So let's go ahead and add that foreign Kikkan strength so well, right, Clint will click on the foreign Keys, will, right click select Create new foreign key.
We need to select our reference table That's going to be the person table
and from the dog's table, we're going to use the person I d you got that? You have to make sure you get the person I d and not the i d by accident. Otherwise it will not be set up correctly,
and we're gonna reference the I d on our reference table, which is a person table
We're gonna leave the other defaults as is and click okay
on so we can see what it's going to. D'oh!
The owner table is dogs. The reference key is person,
and we're tying our personality to the I. D table There.
There's a code for it.
Make sure that altar dogs add constraint. Foreign key
person i d. Of references example dot person column I d. Okay, persist
now, if you look at our gear diagram for dogs, we can see that we added the constraint.
So what does this mean?
Well, this means that if we're going to add a dog
it has There has to be a person I d that matches.
If there is not a person i d that matches, we will not be able to add that record to the dogs table. And that makes sense in the context that we aren't accepting or walking stray dogs that don't have an owner. So perhaps this is the beginning of an application for some kind of dog walking service where you have a person
and the dog has to have an owner to be entered into the system. And, ah,
so that's the strength of foreign keys. You can create rules and constraints on tables that allow you to not enter. Doubt it. That does not make sense in the context of that application
and the context of this application. We have decided it doesn't make sense to add a dog if there isn't a person that matches the person I d.
So that completes this lesson. I hope you're able to get your foreign key working. Um, one minor note. If you're having trouble seeing the e R diagram like this, close D beaver and open that up. Sometimes D Beaver loses track of what it's doing for some reason, and then when you reopen it, the correct settings show up. So if you're having issues, give that a shot.
Outside of that, I hope you got this working, and I hope to see in the next lesson.