Referential integrity lets you define required relationships between and within tables. The database manager maintains these relationships, which are expressed as referential constraints. These relationships require that all values of a given attribute or table column also exist in some other table column.
In general, DB2-enforced referential integrity is much more efficient than coding the equivalent logic in your application program. In addition, having the relationships enforced in a central location in the database is much more powerful than making it dependent upon application logic. You will need indexes to support the relationships that are enforced by DB2.
Referential integrity checking has a cost that is associated with it. Referential integrity is meant for checking parent/child relationships, not code checking. Better options for code checking include check constraints, or even better, to put codes in memory and check them there.
Table check constraints enforce data integrity at the table level. After a table-check constraint has been defined for a table, every UPDATE and INSERT statement involves checking the restriction or constraint. If the constraint is violated, the data record is not inserted or updated, and an SQL error is returned.
A table-check constraint can be defined when you create a table or by using the ALTER TABLE statement. The table-check constraints can help implement specific rules for the data values contained in the table by specifying the values allowed in one or more columns in every row of a table. This practice can save time for the application developer because the validation of each data value can be performed by the database and not by each of the applications accessing the database. However, check constraints should, in general, not be used for data edits in support of data entry. For this scenario, it is best to cache code values locally within the application and perform the edits local to the application. This practice avoids numerous trips to the database to enforce the constraints.
|
Copyright © 2014 CA Technologies.
All rights reserved.
|
|