Ensuring Data Integrity

This chapter explores the following mechanisms in Ingres for enforcing data integrity:

• Integrities
• Rules
• Database events

You can use these mechanisms to enforce a variety of relationships—such as referential integrity and general integrity constraints—or for more general purposes, such as tracking all changes to particular tables or extending the Ingres permission system.

Data integrity in the form of constraints was introduced in the chapter "Managing Tables and Views."

Integrities

The integrity mechanism is similar to the referential, unique, check, and primary key constraints for ensuring data integrity when you create or alter a table.

Constraints Compared with Integrities

Constraints check for appropriate data values whenever data is entered in the table. For more information, see the chapter "Managing Tables and Views."

Integrity refers to integrity objects defined after the table is created to check on update requests before they are allowed to affect the database.

Both mechanisms can be used to ensure data integrity.

Note: Constraints are the ISO Entry SQL92-compliant methods for maintaining database integrity and are, therefore, recommended over integrities. We recommend that you not define both constraints and integrities in the same table.

Differences in Error Handling Between Integrities and Constraints

Constraints and integrities differ in their error-handling characteristics:

• If a constraint is defined for a table, an attempt to update the table with a row containing a value that violates the constraint causes the DBMS to abort the entire statement and issue an error.
• If an integrity is defined for a table, an attempt to update the table with a row containing a value that violates the constraint causes the invalid row to be rejected, but no error is issued.

Important! If you mix constraints and integrities in the same table, the integrities are checked first. If a row violates both an integrity and a constraint, the row is filtered out by the integrity before the constraint is checked, and thus does not generate an error message.

Differences in Null Handling Between Integrities and Constraints

Constraints and integrities handle nulls differently. Check constraints allow nulls by default, whereas integrities do not allow nulls by default. Instructions on how to allow nulls are described in Nulls and Integrities.

Ways to Work with Integrity Objects

You can create and work with integrities using either VDBA or SQL statements.

In VDBA, integrities are implemented using integrity objects. An integrity object defines an automatic check that allows you to closely monitor any update requests before they are allowed to affect the database. Using the Integrities branch for a particular table in the Database Object Manager window, you can:

• Create integrity objects
• View existing integrity objects, including the detailed properties of each individual object
• Drop integrity objects

For detailed steps for performing these procedures, see online help.

Using SQL, you can accomplish these tasks with the create integrity, drop integrity, and help integrity statements. For more information, see the SQL Reference Guide.

How Integrities Are Used

Immediately after you define an integrity object, the table is checked to make sure that the condition is true for all existing rows. If not, an error is returned, and the integrity object is rejected. If your table is very big, it takes some time to scan each row to determine whether the integrity can be applied.

After successfully creating an integrity object, all subsequent operations on the table must satisfy the specified condition. Changes to the database (that is, updates, inserts, and deletes) that are not applied because of an integrity violation are not specifically flagged or reported as errors—they are simply not performed:

• If a change applies to a set of rows, this means that only some of the rows were actually updated.
• If the change is for a single row, a returned row count of zero is a clue that the update did not take place.

Nulls and Integrities

If you create an integrity involving a column that is nullable (has been created using the "with nulls" clause so the user can insert a NULL), the condition must take into consideration the possibility of encountering a null value. For more information on nullable columns, see the chapter "Managing Tables and Views." For example, suppose the number column in a particular table is nullable, and you define an integrity with the following condition that restricts number values to 50 or less:

number <= 50

Null is not in itself a value, so the comparison evaluates to false for any row in which the number column already has a null entry. You must create this integrity on a nullable column before the column contains any nulls. Otherwise, the integrity is rejected. Furthermore, with this integrity defined, the number column, even though it is defined as nullable, does not allow nulls.

To to allow nulls in the column, you need to define the integrity with a null clause to ensure proper handling of nulls with the integrity constraints:

number <= 50 or number is null

The Copy Statement and Enforcing Integrities

If you use the copy statement where integrities are involved, after the copy operation you must check for and replace or delete rows with values violating the integrities. Alternatively, you can copy to a temporary table and create an insert statement that uses a subselect statement on the temporary table.

Note: Constraints defined when you create or alter a table are also ignored in this situation and must be dealt with in a similar manner.