Oracle7 Server Concepts
An Introduction to Integrity Constraints
Oracle uses integrity constraints to prevent invalid data entry into the base tables of the database. You can define integrity constraints to enforce the business rules that are associated with the information in a database. If any of the results of a DML statement execution violate an integrity constraint, Oracle rolls back the statement and returns an error.
Note: Operations on views (and synonyms for tables) are subject to the integrity constraints defined on the underlying base tables.
For example, assume that you define an integrity constraint for the SAL column of the EMP table. This integrity constraint enforces the rule that no row in this table can contain a numeric value greater than 10,000 in this column. If an INSERT or UPDATE statement attempts to violate this integrity constraint, Oracle rolls back the statement and returns an informative error.
The integrity constraints implemented in Oracle fully comply with the standards set forth by ANSI X3.135-1989 and ISO 9075-1989.
Advantages of Integrity Constraints
Integrity constraints are not the only way to enforce data integrity rules on the data of your database. You can also
- enforce business rules using triggered stored database procedures (see Chapter 15, "Database Triggers")
The following section describes some of the advantages that integrity constraints have over these other alternatives.
Declarative Ease
Because you define integrity constraints using SQL commands, when you define or alter a table, no programming is required. Therefore, they are easy to write, eliminate programming errors, and Oracle controls their functionality. For these reasons, declarative integrity constraints are preferable to application code and database triggers. The declarative approach is also better than using stored procedures because, unlike the stored procedure solution to data integrity by controlled data access, integrity constraints do not eliminate the flexibility of ad hoc data access.
Centralized Rules
Integrity constraints are defined for tables (not an application) and stored in the data dictionary. Therefore, the data entered by any application must adhere to the same integrity constraints associated with a table. By moving business rules from application code to centralized integrity constraints, the tables of a database are guaranteed to contain valid data, no matter which database application manipulates the information. Stored procedures cannot provide the same advantage of centralized rules stored with a table, and although database triggers can provide this benefit, the complexity of implementation is far greater than the declarative approach used for integrity constraints.
Maximum Application Development Productivity
If a business rule enforced by an integrity constraint changes, the administrator need only change that integrity constraint and all applications automatically adhere to the modified constraint. Alternatively, if a business rule is enforced by the code of each database application, developers must modify all application source code and recompile, debug, and test the modified applications.
Immediate User Feedback
Because Oracle stores specific information about each integrity constraint in the data dictionary, you can design database applications to use this information to provide immediate user feedback about integrity constraint violations, even before Oracle executes and checks the SQL statement. For example, a SQL*Forms application can use integrity constraint definitions stored in the data dictionary to check for violations as values are entered into the fields of a form, even before the application issues a statement.
Superior Performance
Because the semantics of integrity constraint declarations are clearly defined, performance optimizations are implemented for each specific declarative rule. The Oracle query optimizer can use declarations to learn more about data to improve overall query performance. (Also, taking integrity rules out of application code and database triggers guarantees that checks are only done when necessary.)
Flexibility for Data Loads and Identification of Integrity Violations
Integrity constraints can be temporarily disabled so that large amounts of data can be loaded without the overhead of constraint checking. When the data load is complete, you can easily enable the integrity constraints, and you can automatically report any new rows that violate integrity constraints to a separate exceptions table.
The Performance Cost of Integrity Constraints
The advantages of enforcing data integrity rules do not come without some loss in performance. In general, the "cost" of including an integrity constraint is, at most, the same as executing a SQL statement that evaluates the constraint.