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.
Last modified date: 11/09/2022