More on Primary and Foreign Keys:

Speaker Notes

Slide 1:

This will deal with more primary and foreign keys.

Slide 2:

The student system contains 4 tables:

student00

stucourses00

major00

course00

Slide 3:

We need three fields as the combined key to make each record unique.

Each student takes multiple courses so studentidno is not unique.

Each student can take the same course multiple times so studentidno plus coursecd is not unique.

Studentidno plus coursecd plus semtaken is unique because it allows the same student to take the same course multiple times.

Slide 4:

This is the major table. It is a reference table. I link in to it to grab the name of the major or the name of the chair. The link is via the major code so that is the primary key.

The majorcode can then be set up as a foreign key on the tables from which you want to link in.

Slide 5:

This table functions in the same manner as the major00 table. It will be linked into to get the course name and the credits. The link will be based on the primary key which is coursecd. The coursecd will be carried as a foreign key on the tables that will be linking in.

Slide 6:

I have now created the primary key studentidno_pk for the student00 table. Note that the not null is now part of the describe.

Slide 7:

Oracle does not let you create a key that would violate the integrity of the existing data.

I will delete the duplicate record and then try to create the primary key again.

Slide 8:

Since there were two records that were identical, I deleted them both and then added one back in.

I could then alter the table. The results are shown in the DESC stucouse00.

When records are inserted in this table, they must match the integrity of the primary key which calls for no duplicates and no nulls. The two identical records could never have happened if the primary key had been in place.

Slide 9:

Each of these tables has a single column as the primary key.

Slide 10:

This shows the three primary key constrains created for the stucourse00, major00, and course00 tables.

Slide 11:

Remember that when I establish the foreign key, I am "sitting" in the table that contains the foreign key and linking into another table.

Therefore, I name the foreign key using the name that exists in the table that I am "sitting" in, student00.

I then use the reference clause to name the table that I want to link into. By default, the link will be made to the column on the table that is the primary key.

Slide 12:

This shows the results of looking at the constraints table after the foreign key has been created. Notice another key was added but there is no action enabled on that key.

Slide 13:

Foreign keys must link to primary keys in the table. Notice that the key name is not mentioned when the foreign key is developed. The reference clause simply names the table that the link is to. By default, the link is to the primary key on that table.

Slide 14:

I have now created coursecd as a foreign key on stucourse00 that links to course00.

Slide 15:

I now have established studentidno on the stucourse00 table as a foreign key into student00. This means I will not be allowed to add a record for a student in this table who does not exist on student00.

Slide 16:

Because of the way the keys were named, it is easy to see what are primary keys and what are foreign keys.

Note that the table names are capitalized and in single quotes. This is necessary because the names are being used as data in the lookup and they are stored in capital letters.

Slide 17:

The foreign key that was established in stucourse00 and linked to the studentidno in student00 is the reason for this rejection. The integrity that was violated is an attempt to add a record for a student that does not exist. Adding a record that contains the foreign key is adding a child. Since the record that it links to is missing, the integrity violation is that the parent is missing for the child that I attempted to add.

Notice that in the error message you get the name of the constraint that was violated.

Slide 18:

Again, the table containing the foreign key is considered the child and the table containing the primary key is considered the parent.

coursecd_fk is the foreign key on the child (stucourse00)

coursecd_pk is the primary key on the parent (course00)

Slide 19:

When maintaining a system, it is important to add the records that will be linked to before adding the records that link. In this case I added the CIS40 course where CIS40 is the primary key to the course00 table before adding a record that carries CIS40 as the foreign key to the stucourse00 table.

Slide 20:

This is a tool I find very useful, the treatment on the next slide is also useful in some instances because it includes table name.

Slide 21:

You can do a select * on USER_CONS_COLUMNS to find out what other information is available. If you include a where clause that limits the tables, you will find the information more manageable.

Slide 22:

Here I disabled the coursecd_pk and used the clause CASCADE. This also disables the foreign key from stucourse00. I was then able to add a record to stucourse00 that hand MAR55 as the course even though MAR55 was not in course00.

Slide 23:

This slide continues from the previous slide. When I try to reactivate the foreign key on the stucourse00 table it cannot do it because there is no parent for the MAR55 child I inserted into the table when the constraints were inactive. I can either delete the MAR55 child or add MAR55 to the course00 table.

Slide 24:

Note I could name the primary key in the drop clause.

Slide 25:

I am now recreating the keys that I dropped.