1
00:00:00,440 --> 00:00:03,720
We've learned a lot about unique
keys and primary keys.

2
00:00:03,720 --> 00:00:07,210
Unique keys guaranteed data is
unique within a given column.

3
00:00:07,210 --> 00:00:10,620
Primary keys also guaranteed
the uniqueness within a column but

4
00:00:10,620 --> 00:00:11,980
they go a step further.

5
00:00:11,980 --> 00:00:16,150
They guarantee that each row in
a table can be identified and

6
00:00:16,150 --> 00:00:19,540
thus linked from one table to another.

7
00:00:19,540 --> 00:00:23,231
In this video, we'll focus on another
really important type of key,

8
00:00:23,231 --> 00:00:25,190
the foreign key.

9
00:00:25,190 --> 00:00:27,750
You can think of a foreign
key as a pointer or

10
00:00:27,750 --> 00:00:29,990
reference from one table to another.

11
00:00:29,990 --> 00:00:33,690
With this example the ProductID
column in the cell table

12
00:00:33,690 --> 00:00:37,220
is appointed to the ProductID
in the Products table.

13
00:00:37,220 --> 00:00:42,450
The one in the ProductID column in the
sales table points to the corresponding

14
00:00:42,450 --> 00:00:45,220
one row in the product table.

15
00:00:45,220 --> 00:00:48,290
In this case scarf, purple, wool.

16
00:00:48,290 --> 00:00:52,304
The two points to the row in the product
table that says gloves, white.

17
00:00:52,304 --> 00:00:57,021
Finally the three points to the row
in the product table that says, coat,

18
00:00:57,021 --> 00:00:58,572
women's, long.

19
00:00:58,572 --> 00:01:03,350
The ProductID column in the cell
table is the foreign key.

20
00:01:03,350 --> 00:01:06,570
There are two facets to
creating a foreign key,

21
00:01:06,570 --> 00:01:11,690
while we're not creating a foreign
key ourselves it's good to know, first,

22
00:01:11,690 --> 00:01:18,130
the database designer has to match
the columns properly in both tables.

23
00:01:18,130 --> 00:01:21,330
For example, we just saw that the sale and

24
00:01:21,330 --> 00:01:26,440
product relationship with the ProductID
being referenced in this sales table.

25
00:01:26,440 --> 00:01:31,320
Database systems aren't smart enough to
know that these two columns are related

26
00:01:31,320 --> 00:01:35,330
without an additional instruction
when the tables are created.

27
00:01:35,330 --> 00:01:39,836
This instruction is called Constraint
specifically, a Foreign Key constraint.

28
00:01:39,836 --> 00:01:45,669
[SOUND] You can think of a constraint as
a rule that the database has to enforce,

29
00:01:45,669 --> 00:01:50,788
once the database is instructed to
enforce a foreign key constraint

30
00:01:50,788 --> 00:01:56,801
between two tables, it will not allow the
value to be added to a table containing

31
00:01:56,801 --> 00:02:02,412
a foreign key unless the value also
exists in a table with the primary key.

32
00:02:02,412 --> 00:02:07,405
Being able to ensure the validity of our
data relationships in this manner is

33
00:02:07,405 --> 00:02:09,669
called referential integrity and

34
00:02:09,669 --> 00:02:14,430
is one of the most important aspects
of a good relational database.

35
00:02:14,430 --> 00:02:18,320
Now, that we know what a foreign
key is let's see it in action.

36
00:02:18,320 --> 00:02:20,624
Here we have a table called EyeColor,

37
00:02:20,624 --> 00:02:25,460
It has an integer primary key that
we'll reference under the table.

38
00:02:25,460 --> 00:02:32,006
Lets add two rows to the EyeColor
table now, brown and blue.

39
00:02:32,006 --> 00:02:36,356
Imagine that we have a person table and
we're gonna insert three people,

40
00:02:36,356 --> 00:02:39,110
the first person will give brown eyes.

41
00:02:39,110 --> 00:02:43,290
Designated with the one to much
the value in the primary key column

42
00:02:43,290 --> 00:02:45,440
in the EyeColor table.

43
00:02:45,440 --> 00:02:49,620
The second person will give blue
eyes with the color ID value of two,

44
00:02:49,620 --> 00:02:53,060
the third person will give green eyes.

45
00:02:53,060 --> 00:02:57,970
Let's try and set that record with the
value of three, it shouldn't let us but

46
00:02:57,970 --> 00:02:58,920
it does.

47
00:02:58,920 --> 00:03:03,460
The person symbol has an EyeColorID
value that refers to a record

48
00:03:03,460 --> 00:03:07,670
in the EyeColor table that doesn't
exist with the ID of three.

49
00:03:07,670 --> 00:03:11,660
This would indicate that
the constraints had not been created

50
00:03:11,660 --> 00:03:15,360
on the EyeColorID relationship
between the two tables.

51
00:03:15,360 --> 00:03:19,740
Again, without the constraint, the
database doesn't care if we're trying to

52
00:03:19,740 --> 00:03:24,775
refer to a value that doesn't exist in
the primary key of the EyeColor table.

53
00:03:24,775 --> 00:03:28,781
[SOUND] But if the database has
proper constraints in place,

54
00:03:28,781 --> 00:03:33,746
it will not allow us to insert the row for
Jessica with the eye color of three

55
00:03:33,746 --> 00:03:37,614
until it exists in the primary
key in the EyeColor table.

56
00:03:37,614 --> 00:03:42,124
Now that we've added the green
color to the EyeColor table,

57
00:03:42,124 --> 00:03:47,980
let's try and insert Jessica again
with her green eyes and it works.

58
00:03:47,980 --> 00:03:52,850
In review, we've learned about three
different types of keys, unique,

59
00:03:52,850 --> 00:03:54,450
primary and foreign keys.

60
00:03:55,670 --> 00:04:00,010
Unique keys guarantee that no data
repeats within a particular column.

61
00:04:00,010 --> 00:04:03,250
Primary Keys are like unique keys but
they go further,

62
00:04:03,250 --> 00:04:07,490
they guarantee that each row on
the table can be uniquely identified.

63
00:04:07,490 --> 00:04:11,480
Primary keys also work together with
the last kind of key, foreign keys.

64
00:04:11,480 --> 00:04:15,080
To give us the ability to relate
two tables of data together

65
00:04:15,080 --> 00:04:17,500
on common data values.

66
00:04:17,500 --> 00:04:21,440
Last, we learned that foreign key
constraints are the way that database

67
00:04:21,440 --> 00:04:23,780
is enforce these relationships.

68
00:04:23,780 --> 00:04:29,470
Without foreign key constraints,
our data lacks referential integrity.

69
00:04:29,470 --> 00:04:33,195
There are other types of database keys
as well, check the teacher's notes for

70
00:04:33,195 --> 00:04:35,521
more information on all
types of database keys.