3.17 Foreign Keys

00:00

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.

00:19

And

00:20

this lesson you discuss foreign keys. So what is a foreign key? A foreign key is a column of data

00:25

that is a primary key in another table.

00:29

So

00:31

what? What that allows you to do is communicate

00:33

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.

00:52

Now let's go ahead and create another table and connect it to the person table using a foreign key relationship.

01:00

So left click on tables, right click create new table,

01:03

and we're for a moment going to pretend that we are, Ah, a dog walker with our own application

01:08

and we have a list of people with dogs.

01:11

Um, sorry. If you were a cat lover,

01:15

we might make a cat table later. But for now, we're just going to dogs. We're gonna save the dog table.

01:21

And again, I right clicked on. Ah,

01:23

all right, Yeah, right. Clicked on tables and selected. Create new table to start this process,

01:27

I'm gonna go ahead and make sure I'm in the columns area. I'm gonna right click

01:32

create a new column

01:34

you call it I d.

01:37

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

01:48

we're gonna We're not gonna make it auto increment yet, but we will.

01:51

We're gonna create another new column we call it breed

01:56

on, and we're gonna leave that with the defaults, and then we're gonna click another new column.

02:01

We're gonna call it a name.

02:05

So the dog's name, we're gonna leave that the faults there.

02:07

And now we're going to create a new column on We're going to call this one

02:13

person I d.

02:15

And we're going to say that's a big end. Unsigned.

02:19

It's not allowed to be known,

02:21

and this one's not allowed to be no leader.

02:23

Let's go in and save what we have so far.

02:25

Save, persist.

02:29

We're gonna add a constraint. This is the primary key. Add that primary key. Create new constraint.

02:35

The faults to the primary key. Select the idee click Okay,

02:38

save that.

02:40

Persist.

02:42

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.

02:50

So save, persist

02:55

My thoughts.

02:58

Well, maybe that's still there. So

03:02

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.

03:13

We need to select our reference table That's going to be the person table

03:17

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,

03:27

and we're gonna reference the I d on our reference table, which is a person table

03:31

We're gonna leave the other defaults as is and click okay

03:36

on so we can see what it's going to. D'oh!

03:39

The owner table is dogs. The reference key is person,

03:44

and we're tying our personality to the I. D table There.

03:47

Click Save.

03:50

There's a code for it.

03:52

Make sure that altar dogs add constraint. Foreign key

03:57

person i d. Of references example dot person column I d. Okay, persist

04:02

and we persist that

04:05

now, if you look at our gear diagram for dogs, we can see that we added the constraint.

04:13

So what does this mean?

04:14

Well, this means that if we're going to add a dog

04:18

to the dog table,

04:19

it has There has to be a person I d that matches.

04:24

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

04:44

and the dog has to have an owner to be entered into the system. And, ah,

04:50

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

05:00

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.

05:11

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.

05:30

Outside of that, I hope you got this working, and I hope to see in the next lesson.