Classes of Integrity Constraints

In Chapter 4, we divided integrity constraints into six different types based on their logical levels in the relational model. In this chapter, we'll use a different organization. We'll separate integrity constraints into two classes: intrinsic constraints and business constraints (also known as business rules).

Intrinsic constraints govern the physical structure of the data and are derived from the relational model. The rule that prohibits users from deleting a record from the Customers table if any related records exist in the Orders table is an intrinsic constraint, since referential integrity is a function of the relational model. If a user deletes the customer record without deleting the dependent order records, the database is destabilized. If a customer record is added later that has the same primary key as the deleted customer record, the orphaned order records will be associated—incorrectly—with the new customer record. This can easily happen if you derive the primary key value from the customer's name.

Even if the primary key is never reused, orphan records can cause the database to return spurious results. Queries that calculate the total number of products sold over a given period of time will return different results depending on whether or not the Orders table is joined to the Customers table, for example. For a detailed listing of the number of each product sold to each customer, the Customers and Orders tables are usually joined using a natural join. Only the Orders records that have a matching record in the Customers table will be included in the calculation of total sales. The orphaned Orders records don't have an associated record in the Customers table, so they'll be left out of the calculation. For a summary sales listing that links the Orders table only to the Products table, on the other hand, these orphaned records will be included in the total sales calculation. Thus the system will provide two different answers to the question, "How much did we sell in June?" depending on how the question is phrased, which is clearly unacceptable.

Business constraints, on the other hand, are derived from the problem space. A rule that prohibits a customer record deletion from cascading to the Orders table unless all the relevant orders have been filled or canceled is a business constraint. Business constraints say "we don't do that here," whereas intrinsic constraints say "it can't be done." In practice, the distinction between intrinsic constraints and business rules isn't always clear and isn't at all important. What is important is that one class of data integrity constraint derives from the problem space. You implement these constraints as a convenience to users, and you can safely ignore the constraints when doing so is convenient for the users. Deleting a customer who has open orders isn't convenient—it could very well be a disaster. But adding a sixth employee when a business rule says each manager can have only five employees could be convenient. In fact, not being able to enter that employee could be very inconvenient indeed.

The point is that overruling business constraints won't jeopardize the stability or reliability of the database. If a user deletes a customer record and also deletes associated outstanding orders, the system has lost important data but none of the remaining data is threatened. The relevant Customers and Orders records can be restored or reentered, and everything will be fine.

The two classes of data integrity constraints—intrinsic constraints and business constraints—must be handled differently by the system. Intrinsic constraints can't be ignored without jeopardizing the reliability of the data. Business constraints can and often should be overridden at the discretion of users. We'll examine each class of integrity constraint in detail in the following sections.