We've learned a lot about unique keys and primary keys.

Unique keys guaranteed data is unique within a given column.

Primary keys also guaranteed the uniqueness within a column but

they go a step further.

They guarantee that each row in a table can be identified and

those links from one table to another.

In this video we'll focus on another really important type of key,

the foreign key.

You can think of a foreign key as a pointer or

reference from one table to another.

With this example the ProductID column in the cell table

is appointed to the ProductID in the Products table.

The one in the ProductID column in the sales table points to the corresponding

one row in the product table.

In this case scarf, purple, wool.

The two points to the row in the product table that says gloves, white.

Finally the three points to the row in the product table that says, coat,

women's, long.

The ProductID column in the cell table is the foreign key.

There are two facets to creating a foreign key,

while we're not creating a foreign key ourselves it's good to know, first,

the database designer has to match the columns properly in both tables.

For example, we just saw that the sale and

product relationship with the ProductID being referenced in this sales table.

Database systems aren't smart enough to know that these two columns are related

without an additional instruction when the tables are created.

This instruction is called Constraint specifically, a Foreign Key constraint.

[SOUND] You can think of a constraint as a rule that the database has to enforce,

once the database is instructed to enforce a foreign key constraint

between two tables, it will not allow the value to be added to a table containing

a foreign key unless the value also exists in a table with the primary key.

Being able to ensure the validity of our data relationships in this manner is

called referential integrity and

is one of the most important aspects of a good relational database.

Now, that we know what a foreign key is let's see it in action.

Here we have a table called EyeColor,

It has an integer primary key that we'll reference under the table.

Lets add two rows to the EyeColor table now, brown and blue.

Imagine that we have a person table and we're gonna insert three people,

the first person will give brown eyes.

Designated with the one to much the value in the primary key column

in the EyeColor table.

The second person will give blue eyes with the color ID value of two,

the third person will give green eyes.

Let's try and set that record with the value of three, it shouldn't let us but

it does.

The person symbol has an EyeColorID value that refers to a record

in the EyeColor table that doesn't exist with the ID of three.

This would indicate that the constraints had not been created

on the EyeColorID relationship between the two tables.

Again, without the constraint, the database doesn't care if we're trying to

refer to a value that doesn't exist in the primary key of the EyeColor table.

[SOUND] But if the database has proper constraints in place,

it will not allow us to insert the row for Jessica with the eye color of three

until it exists in the primary key in the EyeColor table.

Now that we've added the green color to the EyeColor table,

let's try and insert Jessica again with her green eyes and it works.

In review, we've learned about three different types of keys, unique,

primary and foreign keys.

Unique keys guarantee that no data repeats within a particular column.

Primary Keys are like unique keys but they go further,

they guarantee that each row on the table can be uniquely identified.

Primary keys also work together with the last kind of key, foreign keys.

To give us the ability to relate two tables of data together

on common data values.

Last, we learned that foreign key constraints are the way that database

is enforce these relationships.

Without foreign key constraints, our data lacks referential integrity.

There are other types of database keys as well, check the teacher's notes for

more information on all types of database keys.