Datasheet

Referential integrity
In addition to establishing relationships between tables, you are able to
enforce certain rules that guide these relationships. For example, if you have
an Invoice table with a Customer_Number foreign key, you will not be able to
add an invoice for a customer number that does not exist in the other table.
You must add the new customer to the customer table before the new foreign
key can be placed in the invoice table. Also, if you attempt to delete a customer
from a table when there are matching invoices for that customer, an error will
occur. Referential integrity allows you to use Access to maintain the relation-
ships that you have created.
By clicking the Enforce Referential Integrity checkbox in the Edit Relation-
ships dialog box, you tell Access to first verify that a valid relationship exists
between the two tables. Here are some conditions that need to be met to estab-
lish referential integrity:
■■
The field that is used to match the two tables must be a primary key in
one of those tables.
■■
The field that is used to match the two tables must be of the same
data type.
Once the validity of the relationship has been established, referential
integrity will be continuously enforced until switched off.
Cascading updates and deletes
The main purpose of referential integrity is two-fold: first, to prevent changing
a primary key value for which there are matching foreign key values in a sec-
ond table, and second, to prevent the deletion of a primary key value for which
there are matching foreign key values. These two rules of referential integrity
can be overridden by clicking either Cascade Update Related Fields or Cas-
cade Delete Related Records. This topic is touched on again in Chapter 2.
Query Basics
Once the data is in Access and the relationships between the tables have been
established, you are ready to start analyzing the data. As you can see on the
Database window, “Queries” is the next main database object. This section
focuses on perhaps the most common type of query: the select query. It also
discusses the basic concept of the query and provides a few examples to illus-
trate just how easy they are to create in Access.
24 Chapter 1
05_59978X ch01.qxp 12/1/05 7:46 PM Page 24