Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I welcome back to Module five sequel development. We are currently in less than two foreign keys, and we're moving into Lesson 2.3,
00:08
the foreign key for the comments table.
00:11
So in the previous lesson, we implemented the foreign key for images and articles. And this lesson we're gonna move on to the next table, which is the comments table, and that will have to foreign key requirements we need to implement.
00:23
And that is
00:25
a restraint or a foreign key constraint to the user table
00:29
and a foreign key constraint to the article table,
00:32
so that when somebody generates a comment,
00:35
we are ensuring that that user exists,
00:39
and that article also exists.
00:42
So we're going to do the same thing that we did for the images table. But we're going to do it twice.
00:47
Let's go ahead, get sorry.
00:48
So if we go to the Properties tab
00:51
and we click on the Foreign Keys, we right click in the empty area and select create new foreign key.
00:58
Let's go ahead. You could do it in either order. The order doesn't matter
01:02
and let's go ahead and do the user's first.
01:03
So we're going to say that the author i d
01:07
references the i D column on the user's table.
01:11
Go ahead and select. Okay, leaving the rest of the options at the default setting.
01:18
And we're gonna go ahead and add in another foreign key.
01:22
And we already did the user. So now we need to reference the articles,
01:26
and we're going to select the article I D. Reference is the i. D.
01:30
Column on the article Stable.
01:34
Select. Okay,
01:36
now go ahead and click. Save
01:38
persist
01:44
should take just a moment.
01:48
And now if we go and look at the R diagram will see that it hasn't updated it.
01:53
And even if you select refresh, it does not seem to update that e r diagram.
01:57
So it seems the best way to do that's actually close the, ah, the table section and then reopen it.
02:12
Okay, it looks like the diagram hasn't updated. Let's go ahead and close that again.
02:16
That's right. Click on the table and select. Refresh
02:24
the way for that to refresh the data base. Instance.
02:29
It's double click on it.
02:49
Okay, I got the diagram to show up correctly. What I actually had to do is closing the comments table
02:55
and refreshing from within. This interface was actually not enough, but I had to do was actually had to close the entire D. Beaver instance and then reopen it. So if you're having trouble with the e R diagram updating, you give that a shot,
03:08
it can be a bit of a pain when that happens. But it just appears to be the behavior of the application on occasion.
03:16
So now we can see
03:19
that the comments is bound to the user stable and the articles I D.
03:24
So when a common is added to the database, the database will enforce the rule that the author I d exists
03:31
as an I. D on the user table and that the article I d
03:37
exists as an I. D in the article tables
03:39
Now you might say,
03:42
Well, is isn't ah
03:44
convention good enough to just ensure that
03:46
you Ah, of course, ad
03:50
comments that belonged to a national user and
03:53
comments that belonged to the actual article and in general, yes. But as you get more programmers on the team,
04:00
someone could accidentally introduced a bad algorithm
04:05
that somehow on occasion adds the
04:10
a comment that belongs to the user. That doesn't exist for some reason, and by enforcing these constraints on the database itself, you can ensure that doesn't happen on any other layer of the application. So it's a good idea to to create those constraints when it makes sense in your application.
04:29
In any case, that completes this lesson. I hope you were able to get those foreign key constraints in, and I will see you in the next lesson. Thank you.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor