This chapter is from the book
This chapter is from the book
This chapter is from the book
Establishing Referential Integrity
As you can see, establishing a relationship is quite easy. Establishing the right kind of relationship is a little more difficult. When you attempt to establish a relationship between two tables, Access makes some decisions based on a few predefined factors:
A one-to-many relationship is established if one of the related fields is a primary key or has a unique index.
A one-to-one relationship is established if both the related fields are primary keys or have unique indexes.
An indeterminate relationship is created if neither of the related fields is a primary key, and neither has a unique index. Referential integrity cannot be established in this case.
As covered earlier in the chapter, referential integrity consists of a series of rules that are applied by the Jet Engine to ensure that the relationships between tables are maintained properly. At the most basic level, referential integrity rules prevent the creation of orphan records in the table on the many side of the one-to-many relationship. After establishing a relationship between a Customers table and an Orders table, for example, all orders in the Orders table must be related to a particular customer in the Customers table. Before you can establish referential integrity between two tables, the following conditions must be met:
The matching field on the one side of the relationship must be a Primary Key field or must have a unique index.
The matching fields must have the same data types (for linking purposes, AutoNumber fields match Long Integer fields). With the exception of Text fields, they also must have the same size. Number fields on both sides of the relationship must have the same size (Long Integer, for example).
Both tables must be part of the same Access database.
Both tables must be stored in the proprietary Access file (.MDB) format (they cannot be external tables from other sources).
The database containing the two tables must be open.
Existing data within the two tables cannot violate any referential integrity rules. All orders in the Orders table must relate to existing customers in the Customers table, for example.
CAUTION
Although Text fields involved in a relationship do not have to be the same size, it is prudent to make them the same size. Otherwise, you will degrade performance as well as risk the chance of unpredictable results when creating queries based on the two tables.
After referential integrity is established between two tables, the following rules are applied:
You cannot enter a value in the foreign key of the related table that does not exist in the primary key of the primary table. For example, you cannot enter a value in the CustomerID field of the Orders table that does not exist in the CustomerID field of the Customers table.
You cannot delete a record from the primary table if corresponding records exist in the related table. For example, you cannot delete a customer from the Customers table, if related records exist in the Orders table (records with the same value in the CustomerID field).
You cannot change the value of a primary key on the one side of a relationship if corresponding records exist in the related table. For example, you cannot change the value in the CustomerID field of the Customers table if corresponding orders exist in the Orders table.
If any of the previous three rules are violated and referential integrity is being enforced between the tables, an appropriate error message is displayed, as shown in Figure 3.7.
){kind=link}
Figure 3.7 An error message when attempting to add an order for a customer who doesn't exist.
Access's default behavior is to prohibit the deletion of parent records that have associated child records and to prohibit the change of a primary key value of a parent record when that parent has associated child records. You can override these restrictions by using the two check boxes available in the Relationships dialog box when you establish or modify a relationship.
The following example enforces referential integrity between the tblCustomers table and the tblOrders table. It illustrates how this affects the process of adding and deleting records.
To open the Relationships window, select the Database window and click Relationships on the toolbar. Double-click on the join line between tblCustomers and tblOrders. Enable the Enforce Referential Integrity check box. Click OK. Repeat the process for the relationship between tblOrders and tblOrderDetails.
Go into tblCustomer and add a couple of records. Take note of the CustomerIDs. Go into tblOrders. Add a couple of records, taking care to assign customer IDs of customers that exist in the tblCustomers table. Now try to add an order for a customer whose customer ID does not exist in tblCustomers. You should get an error message.
Attempt to delete a customer from tblCustomers who does not have any orders. You should get a warning message, but you should be allowed to complete the process. Now try to delete a customer who does have orders. You should be prohibited from deleting the customer. Attempt to change the customer ID of a customer who has orders. You should not be able to do this.
Cascade Update Related Fields
The Cascade Update Related Fields option is available only if referential integrity has been established between the tables. With this option selected, the user can change the primary key value of the record on the one side of the relationship. Instead, when an attempt is made to modify the field joining the two tables on the one side of the relationship, the change is cascaded down to the Foreign Key field on the many side of the relationship. This is useful if the primary key field is modifiable. For example, a purchase number on a purchase order master record might be updateable. If the user modifies the purchase order number of the parent record, you would want to cascade the change to the associated detail records in the purchase order detail table.
NOTE
There is no need to select the Cascade Update Related Fields option when the related field on the one side of the relationship is an AutoNumber field. An AutoNumber field can never be modified. The Cascade Update Related Fields option has no effect on AutoNumber fields.
CAUTION
It is very easy to introduce a loophole into your system accidentally. If you create a one-to-many relationship between two tables but forget to set the Required property of the Foreign Key field to Yes, you allow the addition of orphan records. Figure 3.8 illustrates this point. An order was added to tblOrders without a customer ID being entered. This record is an orphan record because no records in tblCustomers have a customer ID of Null. The problem is eliminated by setting the Required property of the Foreign Key field to Yes.
){kind=link}
Figure 3.8 An orphan record with Null in the Foreign Key field.
Cascade Delete Related Records
The Cascade Delete Related Records option is available only if referential integrity has been established between the tables. With this option selected, the user can delete a record on the one side of a one-to-many relationship, even if related records exist in the table on the many side of the relationship. A customer can be deleted even if the customer has existing orders, for example. Referential integrity is maintained between the tables because Access automatically deletes all related records in the child table.
If you attempt to delete a record from the table on the one side of a one-to-many relationship and no related records exist in the table on the many side of the relationship, you get the usual warning message, as shown in Figure 3.9. On the other hand, if you attempt to delete a record from the table on the one side of a one-to-many relationship and related records exist in the child table, you are warned that you are about to delete the record from the parent table as well as any related records in the child table (see Figure 3.10).
){kind=link}
){kind=link}
Figure 3.9 A message that appears after the user attempts to delete a parent record without related child records.
Figure 3.10 A message that appears after the user attempts to delete a parent record with related child records.
TIP
The Cascade Delete Related Records option is not always appropriate. It is an excellent feature, but you should use it prudently. Although it is usually appropriate to cascade delete from an Orders table to an Order Details table, for example, it generally is not appropriate to cascade delete from a Customers table to an Orders table. This is because you generally do not want all your order history deleted from the Orders table if for some reason you wanted to delete a customer. Deleting the order history causes important information, such as your profit and loss history, to change. It therefore is appropriate to prohibit this type of deletion and handle the customer in some other way, such as marking him as inactive, or archiving his data. On the other hand, if you delete an order because it was canceled, you probably want the corresponding order detail information to be removed as well. In this case, the Cascade Delete Related Records option is appropriate. You need to make the appropriate decision in each situation, based on business needs. The important thing is to carefully consider the implications of each option before making your decision.
With the Cascade Update feature enabled, you are able to update the primary key value of a record that has associated child records. With the Cascade Delete feature enabled, you can delete a parent record that has associated child records. This exercise illustrates the use of Cascade Update and Cascade Delete.
Modify the relationship between tblCustomers and tblOrders. Enable the Cascade Update Related Fields check box. Modify the relationship between tblOrders and tblOrderDetails. Enable the Cascade Delete Related Records check box. There is no need to enable Cascade Update Related Fields because the OrderID field in tblOrders is an AutoNumber field.
Attempt to delete a customer who has orders. You still should be prohibited from doing this because you did not enable Cascade Delete Related Records. Change the customer ID in tblCustomers of a customer who has orders. This change should be allowed. Take a look at the tblOrders table. The customer ID of all corresponding records in the table now should be updated to reflect the change in the parent record.
Add some order details to the tblOrderDetails table. Try to delete any order that has details within the tblOrderDetails table. You should receive a warning, but you should be allowed to complete the process.