The concept of "Foreign Keys" must be understood before we can logically utilize a database that is implemented within a RDBMS. Specific RDBMS operations include adding, updating, and deleting data from files. The foreign keys specify the relationships between files and define what can and can't be logically done in order to maintain what is called "referential integrity."
An object (file) that has a part of a key field (one field in a compound field key) that refers to a complete key in another object (file) is said to have a "Foreign Key." For example:
ENROLL(Student_Number, Course_Code, Course_Grade)
STUDENT(Student_Number, Student_Name, etc.)
COURSE(Course_Code, Course_Description, etc.)
In the ENROLL object, the Course_Code is a foreign key because it is a part of the key field and referes to the entire key field for the COURSE object. Similarly, the Student_Number is a foreign key since it refers to the entire key field for the STUDENT object.
The consequences of these relationships is that the ENROLL object and all of its data cannot validly exist without corresponding data in both the STUDENT and the COURSE objects. For example, consider the enrollment of John Smith, student number 12234, into the Management Information Systems class, code MGT503. We cannot validly enroll John unless his student number (and all dependent data) have first been established in the STUDENT object and the course code (and all dependent data) have been established in the COURSE object. In essence, the valid enrollment of John Smith depends upon the data in the STUDENT and COURSE files.
Think about the above example in this manner. If we were permitted to establish a record in the ENROLL file with John Smith's student number and the MIS course code, then wanted to see who was enrolled and in what course, it would be impossible. The Student_Number in the ENROLL file has no corresponding record in the STUDENT file and therefore cannot provide further information about John Smith. Similarly, the Course_Code in the ENROLL file has no corresponding record in the COURSE file and cannot provide further information. Permitting the entry of the data into the ENROLL file would create what is called a "parentless child." The "child," the record in the ENROLL file, has no "parents" to provide the required additional data to complete the enrollment.
Objects (files) having foreign keys are said to be "members" of a database. Objects (files) to which the foreign keys refer are said to be "owners" - the owners of the key fields to which the foreign keys refer.
Why are foreign keys, relationships, members, and owners important? They establish specific requirements for permitted database operations in order to maintain referential integrity.
| Operation | Owner | Member |
|---|---|---|
| Adding Records | Key must be unique | Unique key and link to owner must be possible (owner exists) |
| Updating | Key must be unique, any changes cannot upset links to members (cannot create parentless children | Unique key, cannot upset links to owner (cannot create parentless children |
| Deleting | Changes cannot upset links to members (cannot create parentless children) | No Restrictions |
From a managerial design perspective, it is a good idea to assume that the RDBMS in use does NOT enforce referential integrity. Further, it is a good idea to assume that the programmer/analyst with whom you are working is not necessarily going to design the system to enforce referential integrity. It is your responsibility to define procedures that enforce referential integrity.
What this means is that you must specifically state in your system design that the system cannot permit the enrollment of a student into a specific course until both the student information and the course information have been previously established in their appropriate files. An error message should be displayed if an attempt to enroll a non-existent student into a non-existent course.