Databases Reference
In-Depth Information
A DBMS must follow rules so that it updates data accurately and consistently. These rules, called integrity
constraints, are categorized as either key integrity constraints or data integrity constraints.
Key integrity constraints consist of primary key constraints and foreign key constraints. Primary key con-
straints, which are governed by entity integrity (as discussed in Chapter 4), enforce the uniqueness of the pri-
mary key. For example, forbidding the addition of a rep whose number matches the number of a rep already
in the database is an example of a primary key constraint. Foreign key constraints, which are governed by ref-
erential integrity (as discussed in Chapter 4), enforce the fact that a value for a foreign key must match the
value of the primary key for some row in a table in the database. Forbidding the addition of a customer whose
rep is not already in the database is an example of a foreign key constraint.
Data integrity constraints help to ensure the accuracy and consistency of individual field values. Types
of data integrity constraints include the following:
●
Data type.
The value entered for any field should be consistent with the data type for that field.
For a numeric field, only numbers should be allowed to be entered. If the field is a date, only a
legitimate date should be permitted. For instance, February 30, 2010, is an invalid date that should
be rejected.
●
Legal values.
For some fields, not every possible value that is of the assigned data type is
legitimate. For example, even though CreditLimit is a numeric field, only the values $5,000.00,
$7,500.00, $10,000.00, and $15,000.00 are valid. For the OrderDate field in the Orders table, Pre-
miere Products might insist that only the current date or a future date is an acceptable value when
an order is updated. In addition, you should be able to specify which fields can accept null val-
ues and which fields can't.
●
Format.
Some fields require a special entry or display format. Although the PartNum field is a
character field, for example, only specially formatted strings of characters might be acceptable.
Legitimate part numbers might have to consist of two letters followed by two digits; this is an
example of an entry format constraint. Users might want the OrderDate field displayed with a
four-digit year value instead of a two-digit year value; this is an example of a display format
constraint.
Integrity constraints can be handled in one of four ways:
1. The constraint is ignored, in which case no attempt is made to enforce the constraint.
2. The responsibility for constraint enforcement is placed on the users. This means that users must
be careful that any updates they make in the database do not violate the constraint.
3. The responsibility for constraint enforcement is placed on programmers. Programmers place into
programs the logic to enforce the constraint. Users must update the database only by means of
these programs and not through any of the built-in entry facilities provided by the DBMS because
these would allow violation of the constraint. Programmers design the programs to reject any
attempt by the users to update the database in a way that violates the constraint.
4. The responsibility for constraint enforcement is placed on the DBMS. The DBA specifies the con-
straint to the DBMS, which then rejects any attempt to update the database in a way that vio-
lates the constraint.
244
Q&A
Question:
Which of these four approaches for constraint enforcement is best?
Answer:
The first approach, ignoring the constraint, is undesirable because it can lead to invalid data in the
database, such as two customers with the same number, part numbers with an invalid format, and invalid credit
limits.
The second approach, user constraint enforcement, is a little better because at least an attempt is made
to enforce the constraints. However, this approach places the burden of enforcement on users. Besides mean-
ing extra work for users, any mistake on the part of a single user, no matter how innocent, can lead to invalid
data in the database.
continued
