Referential Integrity Example
In the Personnel System demonstration application database, there is an example of the Department, Section and Employee files. For these files, the rules required are as follows:
1. A Department Code cannot be deleted from the Department file if there are any records in the Section file which require that Department Code. For example, you should not be able to delete department code ADM unless there are no records in the Section file with code ADM, otherwise all the Sections for ADM have no parent record.
2. Department Code cannot be used in the Sections file unless the Department Code exists in the Department file. For example, you cannot create a new section in a department XYZ unless XYZ exists in the Department file.
3. A Section Code cannot be deleted from the Section file if there are any records in the Employee file which require that Section Code. For example, you should not be able to delete section 01 unless there are no records in the employee file with section code 01, (otherwise the employee would not be able to refer to this record).
4. A Department and Section Code cannot be used in the Employee file unless the codes already exists in the Section file. For example, you cannot add an employee to department XYZ section 01 unless it already exists in the Section file.
To define referential integrity rules, you must understand the business application and the data model. For example, no referential integrity rules are needed between Department and Employee because Employee refers to the Section not the Department. Employee data is based strictly on the Section file which, in turn, is based on the Department file. For example, if you create a department ABC, an employee cannot work for that department ABC until a Section is created.
Also See