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:
Here is what the dog table could look like for dogs that live in these apartments above.
Old English Sheep Dog
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
In the dog table, the Foreign Key is
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.
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.
dog table and click the structure tab and then click the word "Index" on the
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.