• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 11/25/19

Understand foreign key constraints

Log in or subscribe for free to enjoy all this course has to offer!

In the last chapter, we created the database and some tables.

Let's look at what a few rows of the apartment table could look like:

apt_id

apt_number

size

bedrooms

1

2B

900

1

2

3A

1051

2

3

5B

1200

3

Here is what the dog table could look like for dogs that live in these apartments above.

dog_id

apt_id

name

breed

bites

1

3

Stan

Poodle

1

2

1

Fran

Chihuahua

0

3

1

Mister Pickles

Old English Sheep Dog

0

4

6

Lamb

Irish Setter

1

Why use the  apt_number  as the PK of the apartment table?

The primary key is a value that should be both unique and should never change.  It is not outside the realm of possibility that an apartment could change its number - because, for example, an owner buys an adjoining unit and combines them or the apartment is split up into smaller units.

Another reason I'm using the  apt_id  and not  apt_number  is to insure that the PK would be unique.  In the domain diagram in the previous part of this course, there was room for multiple buildings in the condo complex. 2 or more units could have the same  apt_number  because they are different buildings within the complex.  Thus, to foolproof the database, I had made an auto incremented column for  apt_id .

In the dog table, the Foreign Key is  apt_id .

The 2 tables are related by a one-to-many relationship.  A one-to-many relationship is a type of Parent Child relationship.  In the child's table ( dog ), the FK shows who the parent is.  However, the parent table ( apartment ) doesn't know who are its children.  If the parent is deleted (or changes its PK), the child becomes dislocated.

Suppose  apt_id  1 gets deleted from the database because that apartment was torn out of the building. This sounds unlikely, but you never know!

apt_id  1 is then gone from the apartment table.

This causes some problems in the dog table for those poor dogs like Fran and Mister Pickles who, according to the table, still live there. The information is no longer accurate.

Either child records should be deleted when their parents are removed, or the database should prevent the parents from being deleted when there are children that depend on them, or the database should update the children to show that they are orphaned.

To manage this, we set up Foreign Key Constraints.  The Foreign Key Constraint registers the parent / child and Primary Key / Foreign Key relationship between the tables and also sets up what to do when the parent is deleted or changed.

Setting up a Foreign Key Constraint

The first step is to make sure that the Foreign Key is indexed.  When we step up the dog table, we hadn't done that. Let's do that now.  In the apartment table,  apt_id  also needs to be indexed for this to work.  We've already done that when we selected  apt_id  as the Primary Key of apartment table.

Select the  dog  table and click the structure tab and then click the word "Index" on the  apt_id  line.

You can see which columns are indexed in your table by clicking the word index at the bottom of the table:

It's tiny, but then it will open a box where you can verify which keys are indexed.  Click the word Indexes again to close that box.

Then at towards the top of the screen, click the word Relational View.

And select the pulldown menus that I have in the image below.

Now, if I try to delete an apartment record, the operation will be rejected because the ON DELETE pulldown menu has RESTRICT selected.

If I had chosen CASCADE in the pulldown menu, when an apartment is deleted, all the dogs associated with that apartment automatically get  deleted. Poor dogs!

If I had selected NULL in this list, it would have filled in the  aptNumber  in the dog table with  NULL  when an apartment is deleted.

Putting in a Foreign Key Constraint for the FK's in a table is recommended to preserve your data's integrity, and data with integrity is what we are all about!

Next, let's go into some detail about that huge list of data types that you saw in the previous chapter.

Example of certificate of achievement
Example of certificate of achievement