Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Start a free Basic trial
to watch this video
Let's look at another core design concept of a relational database, the Foreign Key. Foreign Keys are a column, or columns, that relate records back to the primary key in another table.
Resources:
Here is an explanation and graphical representation of the Foreign Key relationships between three tables
-
0:00
We've learned a lot about unique keys and primary keys.
-
0:03
Unique keys guaranteed data is unique within a given column.
-
0:07
Primary keys also guaranteed the uniqueness within a column but
-
0:10
they go a step further.
-
0:11
They guarantee that each row in a table can be identified and
-
0:16
those links from one table to another.
-
0:19
In this video we'll focus on another really important type of key,
-
0:23
the foreign key.
-
0:25
You can think of a foreign key as a pointer or
-
0:27
reference from one table to another.
-
0:29
With this example the ProductID column in the cell table
-
0:33
is appointed to the ProductID in the Products table.
-
0:37
The one in the ProductID column in the sales table points to the corresponding
-
0:42
one row in the product table.
-
0:45
In this case scarf, purple, wool.
-
0:48
The two points to the row in the product table that says gloves, white.
-
0:52
Finally the three points to the row in the product table that says, coat,
-
0:57
women's, long.
-
0:58
The ProductID column in the cell table is the foreign key.
-
1:03
There are two facets to creating a foreign key,
-
1:06
while we're not creating a foreign key ourselves it's good to know, first,
-
1:11
the database designer has to match the columns properly in both tables.
-
1:18
For example, we just saw that the sale and
-
1:21
product relationship with the ProductID being referenced in this sales table.
-
1:26
Database systems aren't smart enough to know that these two columns are related
-
1:31
without an additional instruction when the tables are created.
-
1:35
This instruction is called Constraint specifically, a Foreign Key constraint.
-
1:39
[SOUND] You can think of a constraint as a rule that the database has to enforce,
-
1:45
once the database is instructed to enforce a foreign key constraint
-
1:50
between two tables, it will not allow the value to be added to a table containing
-
1:56
a foreign key unless the value also exists in a table with the primary key.
-
2:02
Being able to ensure the validity of our data relationships in this manner is
-
2:07
called referential integrity and
-
2:09
is one of the most important aspects of a good relational database.
-
2:14
Now, that we know what a foreign key is let's see it in action.
-
2:18
Here we have a table called EyeColor,
-
2:20
It has an integer primary key that we'll reference under the table.
-
2:25
Lets add two rows to the EyeColor table now, brown and blue.
-
2:32
Imagine that we have a person table and we're gonna insert three people,
-
2:36
the first person will give brown eyes.
-
2:39
Designated with the one to much the value in the primary key column
-
2:43
in the EyeColor table.
-
2:45
The second person will give blue eyes with the color ID value of two,
-
2:49
the third person will give green eyes.
-
2:53
Let's try and set that record with the value of three, it shouldn't let us but
-
2:57
it does.
-
2:58
The person symbol has an EyeColorID value that refers to a record
-
3:03
in the EyeColor table that doesn't exist with the ID of three.
-
3:07
This would indicate that the constraints had not been created
-
3:11
on the EyeColorID relationship between the two tables.
-
3:15
Again, without the constraint, the database doesn't care if we're trying to
-
3:19
refer to a value that doesn't exist in the primary key of the EyeColor table.
-
3:24
[SOUND] But if the database has proper constraints in place,
-
3:28
it will not allow us to insert the row for Jessica with the eye color of three
-
3:33
until it exists in the primary key in the EyeColor table.
-
3:37
Now that we've added the green color to the EyeColor table,
-
3:42
let's try and insert Jessica again with her green eyes and it works.
-
3:47
In review, we've learned about three different types of keys, unique,
-
3:52
primary and foreign keys.
-
3:55
Unique keys guarantee that no data repeats within a particular column.
-
4:00
Primary Keys are like unique keys but they go further,
-
4:03
they guarantee that each row on the table can be uniquely identified.
-
4:07
Primary keys also work together with the last kind of key, foreign keys.
-
4:11
To give us the ability to relate two tables of data together
-
4:15
on common data values.
-
4:17
Last, we learned that foreign key constraints are the way that database
-
4:21
is enforce these relationships.
-
4:23
Without foreign key constraints, our data lacks referential integrity.
-
4:29
There are other types of database keys as well, check the teacher's notes for
-
4:33
more information on all types of database keys.
You need to sign up for Treehouse in order to download course files.
Sign up