Oracle 8: Referential Integrity

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Implementing Referential Integrity is an extremely important part in the design and development of any relational database system. Referential Integrity is the process of automatically maintaining the correctness of your data when modifications are made to fields which are related to other fields in other tables. Having the rules of referential integrity enforced on your database will keep related records from becoming orphaned by its parent records, and can also help to enforce your systems business rules.

There are two basic rules of Referential Integrity. The first is the Delete Cascade. This will delete all child records when a parent record is deleted. The second rule is the Update Cascade. This rule updates all child records to reflect the update made to the parent. We will cover examples of both of these rules as applied to an Oracle8 database. A delete cascade is quite simple to setup. Oracle has this function built right in. When creating a table the administrator needs to identify the foreign keys and the referencing table and then check the cascade on delete option. In the Oracle Schema Manager choose the table constraints tab for the table in question. Then create a foreign constraint on your foreign key columns. Choose the appropriate schema and parent table and then check the cascade on delete option. It is simple as that. Deletes can be automated in such a fashion because there is little question as to what has to be done. If you want to delete related records its very straight forward. This is not the case for update cascades.

In order to create an update cascade within your application you will need to be aware of all primary keys and their foreign keys in other tables. The example we will use is loosely based on an event that took place at my University while I was an undergraduate student, it is an excellent example of both how to implement referential integrity and of why it is important. The system in question in this example is an enrollment database used by a University. The part of the system that we are concerned about consists of four entities. The Students, Students_Courses, Courses, and Departments tables.

We will first set up a simple update cascade to show how one is created. In this case, a course name will be changed in the Courses table and since a number of students have already enrolled in the course these changes must be reflected in the Students_Courses table where each students enrollment information is stored. Before we go any farther, it’s a good idea to take a look at the contents of the Courses and the Students_Courses tables.

The school wants to change the course id of the Logistics course from MLM101 to MSC101. Easy enough, but as can be seen from the table below, this change must be reflected in the Students_Courses table below or the students currently enrolled in MLM101 will not be enrolled in MSC101.

Contents of the Students_Courses Table

To remedy this problem an update cascade trigger should have been created on the Courses table. In this situation we will assume that it has not been created and we will create it ourselves. The code to create the trigger is as follows:

CREATE OR REPLACE TRIGGER DBO.UPDATE_COURSES

AFTER UPDATE OF COURSE_ID ON DBO.COURSES

REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW

DECLARE

  V_NEWID VARCHAR2 (6);

  V_OLDID  VARCHAR2 (6);

BEGIN

  V_NEWID := :NEW.COURSE_ID;

  V_OLDID := :OLD.COURSE_ID;

  UPDATE STUDENTS_COURSES

  SET COURSE_ID = V_NEWID

  WHERE COURSE_ID = V_OLDID;

END;

The second line of the code tells us that the trigger will fire after every update to the course_id field in the Courses table. The third line states that the value of the course_id field prior to the update will be referred to as old and the newly updated value will be referred to as new inside the trigger body. Then we declare a couple of variables and assign them the new and old values. Lastly we update the course_id field in the Students_Courses table with the new course_id wherever the field is equal to the old course_id. So for our example when MLM101 is changed to MSC101 this trigger will update all records where the course_id is equal to MLM101 with the new value MSC101. Now is a good time to test our work so far. If we run the SQL statement below the Students_Courses table should automatically be updated also:

Update courses

	Set course_id = ‘MSC101’

	Where course_id = ‘MLM101’

Notice the current contents of the Courses table shown below, the course_id for the Logistics class, MLM101, has now been replaced with

Now view the new contents of the Students_Courses table and you will see that all instances where course_id was equal to MLM101 have been updated with the new value MSC101.

Check back next week as Scott will take us one step further to allow rules of referential integrity to not only cascade updates but to also enforce our business rules.

About the author: