-
Title:
Foreign Keys - Intro to Relational Databases
-
Description:
-
In database terminology, a column
with a references constraint on it,
-
is also called a foreign key.
-
Now this has always sounded like
a bit of a weird term to me,
-
like it was the key to a lock
from far across the sea, but
-
a foreign key is just a column or
a set of columns in one table,
-
that uniquely identifies
a row in another table.
-
It's possible for
-
a table to have two or more foreign
keys, actually that's really common.
-
For instance, consider a database of
students grades in various courses.
-
Here we've got a table of students
names and their ID numbers.
-
And the ID is a primary key
because names aren't unique.
-
And we have a table of courses, with
course names and course IDs as well.
-
And then we have the grades table,
-
which has foreign keys
into both of those tables.
-
Here are the same tables,
but this time drawn out.
-
Usually a foreign key will point to
the primary key of the referenced table.
-
That's because the whole point of
a foreign key is to uniquely identify
-
a row in the referenced table, and
the table's primary key does that.
-
Now here's a quiz.
-
These tables are for
-
a more complex forum app than
the one you worked on last lesson.
-
This app allows users to log in and
post comments, and
-
also vote on other people's posts.
-
Take a look at these tables, and see if
you can figure out which columns should
-
be primary keys, and which columns
should be foreign keys referencing them.
-
In the users table,
we have a username or handle or
-
alias, and the user's full name.
-
In this posts table,
-
we have the post content, the author's
username, and an ID number for the post.
-
And in the votes table,
we have the id number of a post,
-
the username of somebody who
voted on that post, and a 1 or
-
minus 1 for whether they liked it or
disliked it.
-
So for each one of these columns, if it
should be a primary key of that table,
-
put a P in that box.
-
If it should be a foreign key, put an F.
-
If it shouldn't be either one,
leave the box empty.
-
Also, although there's no check boxes
for them, think about if any of
-
these tables should have primary
keys with more than one column.