Integrity is all about maintaining a consistent state of your data. Here are some types of integrity that can be defined on data.
Entity Integrity
Entity integrity involves the structure (primary key and its attributes) of the entity. If the primary key is unique and all attributes are scalar and fully dependent on the primary key, then the integrity of the entity is good. In the physical schema, the table’s primary key enforces entity integrity. Essentially, entity integrity is normalization.
Domain Integrity
In relational theory terms, a domain is a set of possible values for an attribute, such as integers, bit values, or characters. Domain integrity ensures that only valid data is permitted in the attribute. Nullability (whether a null value is valid for an attribute) is also a part of
domain integrity. In the physical schema, the data type and nullability of the row enforce domain integrity.
Referential Integrity
A subset of domain integrity, referential integrity refers to the domain integrity of foreign keys. Domain integrity says that if an attribute has a value, then that value must be in the domain. In the case of the foreign key, the domain is the list of values in the related primary key.
Referential integrity, therefore, is not an issue of the integrity of the primary key but of the foreign key.
The nullability of the column is a separate issue from referential integrity. It’s perfectly acceptable for a foreign key column to allow nulls.
Several methods of enforcing referential integrity at the physical-schema level exist. Within a physical schema, a foreign key can be enforced by declarative referential integrity (DRI) or by a custom trigger attached to the table.
User-Defined Integrity
Besides the relational theory integrity concerns, the user-integrity requirements must also be enforced, as follows:
✦ Simple business rules, such as a restriction to a domain, limit the list of valid data entries. Check constraints are commonly used to enforce these rules in the physical schema.
✦ Complex business rules limit the list of valid data based on some condition. For example, certain tours may require a medical waiver. Implementing these rules in the physical schema generally requires stored procedures or triggers.
Some data-integrity concerns can’t be checked by constraints or triggers. Invalid, incomplete, or questionable data may pass all the standard data-integrity checks. For example, an order without any order detail rows is not a valid order, but no automatic method traps such an order. SQL queries can locate incomplete orders and help in identifying other less measurable data-integrity issues, including the following:
