9. Ensuring Data Integrity : Rules : Enforcing General Integrities
 
Share this page                  
Enforcing General Integrities
To set up tables that maintain data calculated from other tables, use views on normalized tables. For functional, performance, or data distribution reasons, the derived data must be maintained in another table or even in a specific column of the same table.
A general integrity is any integrity check that is not a referential integrity. General integrities can be used, for instance, to describe the relationship between the original data and the derived data, and a rule can be used to enforce the described relationship.
For example, consider two tables, employee and department. The employee table contains employee information, including the name of the department in which each employee works. The department table includes the number of employees in each department. Given these tables, a useful general constraint is that the number of employees listed for a row in the department table must match the number of employees in the employee table who work in that department.
This constraint can be enforced using rules to correctly update a row in the department table whenever an employee is hired, leaves, or changes departments. For example, if you create a database procedure that updates the department table whenever a new employee is hired, define a rule to invoke it after an insert, passing the department number as a parameter.