Database Reference
In-Depth Information
The third approach removes the burden of enforcement from users and places it on programmers. This
solution is better still because it means that users can't violate the constraints. The disadvantage is that all
update programs in the system become more complex. This complexity makes programmers less productive
and makes programs more difficult to create and modify. This approach also makes changing an integrity
constraint more difficult because this may mean changing all the programs that update the database. Fur-
thermore, if the logic in any program used to enforce the constraints is faulty, the program could permit
some constraint to be violated, and you might not realize that this had happened until a problem occurred at
a later date. Finally, you would have to guard against a user bypassing the programs in the system in order to
enter data directly into the database—for example, by using some built-in facility of the DBMS. If a user is
able to bypass the programs and enters incorrect data, all the controls that were so diligently placed into the
programs are helpless to prevent a violation of the constraints.
The best approach is the one the DBMS enforces. You specify the constraints to the DBMS, and the
DBMS ensures that they are never violated.
249
Nearly all DBMSs include most of the necessary capabilities to enforce the various types of integrity con-
straints. Consequently, you let the DBMS enforce all the constraints that it is capable of enforcing; then let
application programs enforce any other constraints. You also might create a special program whose sole pur-
pose is to examine the data in the database to determine whether any constraints have been violated. You
d
run this program periodically and take corrective action to remedy any violations that the program discovers.
'
NOTE
Access supports key constraints. Access lets you specify a primary key, and then it builds a unique index automatically for the
primary key. Access also lets you specify foreign keys, and then it enforces referential integrity automatically. You can use
Access to specify data integrity constraints. As shown in Figure 7-16, you can specify the data type for each field, and you can
specify data format and legal-values integrity constraints.
Primary
key integrity constraint,
indicated by the key
symbol
Data type
integrity
constraints
Data format
integrity
constraints
Data
legal-values
integrity
constraint
FIGURE 7-16
Example of integrity constraints in Access
