Transition Integrity in Relational Database

You would use transitional integrity constraints, for instance, to ensure that the status of a given order never changed from “Entered” to “Completed” without passing through the interim states, or to prevent a canceled order from changing status at all.

The status of an entity is usually controlled by a single attribute. In this case, transition integrity can be considered a special type of domain integrity. Sometimes, however, the valid transitions are controlled by multiple attributes or even multiple relations. Because transition constraints can exist at any level of granularity, it’s useful to consider them a separate type of constraint during preparation of the data model.

For example, the status of a customer might only be permitted to change from “Normal” to “Preferred” if the customer’s credit limit is above a specified value and he or she has been doing business with the company for at least a year. The credit limit requirement would most likely be controlled by an attribute of the Customers relation, but the length of time the customer has been doing business with the company might not be explicitly stored anywhere. It might be necessary to calculate the value based on the oldest record for the customer in the Orders relation.