Getting to Know MS Access 2003, Part 2 - Relationships
(Page 3 of 5 )
Tables can be related to each other in any one of the three ways: one-to-one, one-to-many, and many-to-many. The type of relationship you use depends on the quantity of records in each of the tables that is going to have the same value. Let’s look at relationships in more detail.
When you relate tables, at least one of the tables must have a unique value for every record. This value should uniquely identify that record. This unique value is generally called a primary key, but it also may be some other unique index with no duplicate values. Moreover it can be a combination of fields that make a unique value for each record, such as a person’s first, middle, and last name, and their city of residence combined.
You will almost always use one-to-many relationships, however. A one-to-many relationship basically says that records in the parent table will have multiple matching records in the child table or none at all. The field, in the child table, that references the parent table is defined as the foreign key of the child table. This key uniquely identifies the record that is referred to in the parent table.
In the above image you can see multiple one-to-many relationships in effect. These are represented by lines with 1’s at one end and ‘infinity’ symbols at the other. The 1 side represents the parent table, while the ‘infinity’ side represents the child table in a particular relationship. I’ll show you how to create relationships in a later lesson.
Referential Integrity
Referential integrity is an optional rule in Access. It guarantees your table relationships are legitimate, and the database will remain functional as you manipulate it. Here are some of the rules of referential integrity:
- There can be no child tables without a parent table
- A parent table cannot be deleted if child tables exist
- A child record cannot be changed so that its foreign key doesn’t have a valid match in a parent table
- The primary key of the parent table cannot be changed if it is referenced by children tables
It’s a good idea to enforce these rules and I’ll show you how to set referential integrity later on.
Database Objects
Because Access is an object-oriented database, it’s composed entirely of objects. Each of these objects, such as tables or forms, has properties that allow you to manipulate their look and feel. Moreover, each Access object holds other objects, which in turn have other properties. For example, tables are composed of fields (objects), with properties such as size and format.
Next: Designing Your First Database >>
More Microsoft Access Articles
More By Sage Adams
/ 20 
