Testing Referential Integrity & Printing and Deleting Table Relationships

The Cascade Delete Related Records option

Because a record doesn't exist in the tblCustomers table, referential integrity will not let you add an orphan record.

In this lesson you will get to test the referential integrity rules you established in the previous lesson. You just went over it, but since this referential integrity stuff is a little tricky and confusing at first, let's review...

Once you have established referential integrity, the following rules are set:

• You can't add a record to a related table unless a matching record already exists in the primary table.

• You can't change the value of a primary key in the primary table if matching records exist in the related table (unless you select the ).

• You can't delete a record from a primary table if matching records exist in a related table (unless you select the Cascade Delete Related Records option).

Think you have a better understanding of referential integrity yet? No? This lesson will give you a chance to work with some related tables where referential integrity has been set. You will also learn how to delete a relationship between two tables.

1. Open the tblCustomers tablein Datasheet View.

   First let's try deleting a record in the tblCustomers table.

2. Click on the record selector for the record with the CustomerID 1 (Antonio Rommero) and click the Delete Record button on the toolbar.

   Because you enabled both referential integrity and cascaded deletes in the previous lesson, Access displays the dialog box shown in figure. Access wants you to confirm your deletion, as deleting the customer record will delete any related data for the customer in the tblCustomerTours table.

3. Click Yes.

   Access deletes the record from the tblCustomers table and any related tours for the customer in the tblCustomerTours table. If you hadn't enforced referential integrity between these tables, you would be left with one or more "orphan" records for Antonio Rommero in the tblCustomerTours table.

4. Close the tblCustomers table and open the tblCustomerTours tablein Datasheet view.

   Yikes! The tblCustomerTours table contains a lot of ID number fields, such as CustomerID and TourID. Unless you have a printed a list of these ID numbers (or have a super-human memory), you won't know which CustomerID to enter. (By the way, this is why you will want to use a lot of forms and queries in relational databasesto hide all the technical inner workings of a database from your hapless users.)

   Anyway, let's try adding a new record to the tblCustomerTours table.

5. Click the New Record button on the Record Navigation bar.

   Access adds a new blank record to the table.

6. Enter a new record using the information in the following tables.

   Before you finish adding the record, it's very important that you remember that you deleted the CustomerID 1 record back in Steps 2 and 3. Since you enforced referential integrity between the two related CustomerID fields, move on to Step 7 to find out what happens when you try to add the record.

   CustomerID	EmployeeID	TourID	Number of Tickets	Date	First Class	Smoker	Ship Via	Cost
   1	4	2	1	4/2/00	0	0	FedEx	450

   
   

7. Finish adding the record by pressing Tab or clicking in any other record when you have finished entering the record's information.

   Because a CustomerID 1 doesn't exist in the tblCustomers table, a dialog box appears, as shown in figure, informing you that Access can't add the record because doing so would violate referential integrity.

8. Click OK to close the dialog box.

   You can cancel the addition of the new record.

9. Press Esc to cancel the new record, then close the tblCustomerTours table.

   Now that you have a better feel for how referential integrity works, let's look at the other two topics in this lessonprinting and deleting table relationships.

10. Click the Relationships button on the toolbar.

    Sometimes you may want a printed hard copy of the Relationships window.

11. Select File» Print Relationships from the menu.

    A report appears in Print Preview. You don't actually need to print anything for now, so...

12. Click the Close button to close the report without saving changes.

    Sometimes you may want to delete the relationship between two tables. Access is very restrictive about letting you modify a related table, and often you must temporarily delete the relationship between two tables, modify one of the tables, and then re-connect them. Here's how to delete a table relationship.

13. Click the join line between the tblTours table and the tblCustomerTours table to select it and press Delete.

    Clicking a tiny join line between two tables requires a lot of mouse dexterityyou probably will have to try several times before you get it.

    Access asks you to confirm the deletion. Let's leave the table relationship be for now.

14. Click No and click the Close button to close the Relationships window.

TO PRINT THE RELATIONSHIPS WINDOW:

1. IN THE DATABASE WINDOW, CLICK THE RELATIONSHIPS BUTTON ON THE TOOLBAR.

   OR...

   RIGHT-CLICK ANY BLANK AREA IN THE DATABASE WINDOW AND SELECT RELATIONSHIPS

2. SELECT FILE » PRINT RELATIONSHIPS FROM THE MENU.

3. MODIFY THE REPORT THAT APPEARS IN PRINT PREVIEW, IF NECESSARY, AND THEN CLICK THE PRINT BUTTON ON THE TOOLBAR.

TO DELETE A TABLE RELATIONSHIP:

1. OPEN THE RELATIONSHIPS WINDOW.

2. CLICK THE JOIN LINE THAT CONNECTS THE TABLES AND PRESS DELETE.

3. CLICK YES TO CONFIRM THE DELETION.