Database Reference
In-Depth Information
PROVIDE DATA INTEGRITY FEATURES
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
constraints, which are governed by entity integrity (as discussed in Chapter 4), enforce the uniqueness of the
primary 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 gov-
erned by referential 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 cus-
tomer 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:
248
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, 2013, is an invalid date and
should be rejected.
Legal values. For some fields, not every possible value that is of the assigned data type is legiti-
mate. 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,
Premiere 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
values 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
constraint to the DBMS, which then rejects any attempt to update the database in a way that
violates the constraint.
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
