dcsimg
March 30, 2017
Hot Topics:

Oracle 8: Referential Integrity

  • July 15, 1999
  • By Scott Haley
  • Send Email »
  • More Articles »


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.

 COURSE  NAME  CREDITS  INSTR  ROOM  DEPT
 MLM101 Logistics 3 mlmprof R100 MLM
 MLM201 Advanced Logistics 3 mlmprof R200 MLM
 ACC101 Financial Accounting 3 accprof R410 ACC
 ACC321 Accounting Systems 3 accprof R220 ACC
 MKT210 Intro to Mktg 3 mktprof R540 MKT
 CPS230 Intro to Algorithms 4 cpsprof C501 CPS
 CPS260 Discrete Structures 3 mthprof C501 CPS
 MIS260 Systems Analysis 3 misprof M501 MIS
 MIS310 Web & E-Commerce 3 misprof M521 MIS
 MIS110 Database Models 3 misprof M521 MIS

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

 STUDENT_ID  COURSE_ID
 1000 CPS260
 1000 MIS260
 1000 MIS310
 1000 ACC321
 2000 MLM101
 2000 ACC101
 2000 MKT210
 2000 MIS260

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

 COURSE  NAME  CREDITS  INSTR  ROOM  DEPT
 MSC101 Logistics 3 mlmprof R100 MLM
 MLM201 Advanced Logistics 3 mlmprof R200 MLM
 ACC101 Financial Accounting 3 accprof R410 ACC
 ACC321 Accounting Systems 3 accprof R220 ACC
 MKT210 Intro to Mktg 3 mktprof R540 MKT
 CPS230 Intro to Algorithms 4 cpsprof C501 CPS
 CPS260 Discrete Structures 3 mthprof C501 CPS
 MIS260 Systems Analysis 3 misprof M501 MIS
 MIS310 Web & E-Commerce 3 misprof M521 MIS
 MIS110 Database Models 3 misprof M521 MIS

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.

 STUDENT_ID  COURSE_ID
 1000 CPS260
 1000 MIS260
 1000 MIS310
 1000 ACC321
 2000 MSC101
 2000 ACC101
 2000 MKT210
 2000 MIS260

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:

Scott Haley currently works as a consultant in Michigan. He utilizes both Oracle and Microsoft technologies to create custom solutions for his employer's clients.

 






Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date
Rocket Fuel