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