Home

Foreign Keys
 

Introduction

We stated earlier that the essence of a relational database was to get the information to flow from one list to another. To make this possible, a relationship must be created between both tables. After creating a primary key on the first table, to prepare the other table for the relationship, it must have a column that would hold and manage this relationship. This column would be used to "represent" the records of the first table. For this reason, such a column is referred to as a foreign key.

To create a foreign key, on the table that would receive the data, add a column that holds the same data type as the column of the primary key of the first table. Also, the name of the foreign key is usually the same as the name of the column that holds the primary key in the other table.

Practical Learning Practical Learning: Creating Foreign Keys
  1. In the Tables section of the Database window, right-click Customers and click Design View
  2. Under the Field Name, right-click AccountName and click Insert Rows
  3. In the new empty field, type AccountTypeID and press Tab
  4. Set its Data Type to Number and press F6
  5. In the lower section of the window, click Caption and type Account Type
  6. Save and close the table
  7. In the Tables section of the Database window, make sure Customers is selected and, on the Database toolbar, click the New Object: AutoForm button
  8. Switch the form to Design View and position the controls as follows:
     
  9. Close the form
  10. When asked whether you want to save it, click Yes and click OK
 

A Foreign Key From the Lookup Wizard

Creating a column that has the same name and data type as its corresponding primary key is the traditional technique of adding a foreign key to a table. Microsoft Access provides another technique that assists you with configuring the relationship between the primary key from the original table and the foreign key from the new table. To use it, after creating a column, set its Data Type to Lookup Wizard. A wizard would start where you can select the table that holds the primary key and the column that would show the desired value of the first table.

Practical Learning Practical Learning: Using the Lookup Wizard

  1. In the Tables section of the Database window, right-click Transactions and click Design View
  2. In the top section of the window, click TransactionID
  3. In the lower section of the window, click Caption and type Transaction Number
  4. In the top section of the window, click TransactionDate
  5. In the lower section, change the following properties
    Format: dd-mmm-yyyy
    Input Mask: 00\->L<LL\-0000;0;_
    Default Value: =Date()
    Indexed: Yes (No Duplicates)
  6. In the top section of the window, click EmployeeID and set its Data Type to Lookup Wizard
  7. In the first page of the wizard, accept the first radio button and click Next
  8. In the second page of the wizard, click Employees and click Next
  9. In the Available Fields list, double-click FullName, Title, and CanCreateNewAccount
  10. Click Next three times and click Finish
  11. When asked to save the table, click Yes
  12. Set the Caption to Processed By
  13. In the top section of the window, click CustomerID and set its Data Type to Lookup Wizard
  14. In the first page of the wizard, accept the first radio button and click Next
  15. In the second page of the wizard, click Customers and click Next
  16. In the Available Fields list, double-click AccountNumber and AccountName
  17. Click Next three times and click Finish
  18. When asked to save the table, click Yes
  19. Set the Caption to Customer
  20. In the top section of the window, click Transaction TypeID and set its Data Type to Lookup Wizard
  21. In the first page of the wizard, accept the first radio button and click Next
  22. In the second page of the wizard, click TransactionTypes and click Next
  23. In the Available Fields list, double-click TransactionType
  24. Click Next three times and click Finish
  25. When asked to save the table, click Yes
  26. Set the Caption to Trans Type
  27. In the top section of the window, click DepositAmount
  28. In the lower section, change the following properties
    Format: Fixed
    Caption: Deposit
  29. In the top section of the window, click DepositTypeID and set its Data Type to Lookup Wizard...
  30. In the first page of the wizard, accept the first radio button and click Next
  31. In the top section of the window, click DepositTypes and click Next
  32. In the top section of the window, click DepositType, click Next three times and click Finish
  33. Set its Caption to Deposit Type
  34. In the top section of the window, click WithdrawalAmount
  35. In the lower section, change the following properties
    Format: Fixed
    Caption: Withdrawal
  36. In the top section of the window, click WithdrawalTypeID and set its Data Type to Lookup Wizard...
  37. In the first page of the wizard, accept the first radio button and click Next
  38. In the second page of the wizard, click WithdrawalTypes and click Next
  39. In the Available Fields list, double-click WithdrawalType, click Next three times and click Finish
  40. When asked to save the table, click Yes
  41. Set its Caption to Withdrawal Type
  42. In the top section of the window, click ServiceCharge
  43. In the lower section, change the following properties
    Format: Fixed
    Caption: Charge
  44. In the top section of the window, click ChargeReasonID and set its Data Type to Lookup Wizard
  45. In the first page of the wizard, accept the first radio button and click Next
  46. In the second page of the wizard, click ChargeReasons and click Next
  47. In the Available Fields list, double-click ChargeReason, click Next three times and click Finish
  48. When asked to save the table, click Yes
  49. Set its Caption to Charge Reason
     
  50. Save the table and close it

The Relationships Window

A valuable utility you can use to create and manage the relationships of your tables is the Relationships window. Before using it, you should have created the necessary tables of your database. You also should have created the primary and foreign keys of the tables whose relationships you want to configure.

To access the Relationships window, when displaying the Database window, on the Database toolbar, you can click the Relationships button. Depending on your database, a Show Table may appear with the list of tables of your database. The Show Table dialog box may not appear too. In this case, to display it, you can right-click the window and click Show Table... From the Show Table dialog box, to add a table to the window, click it and click Add. After adding the tables, click Close.

When adding the tables to the Relationships window, if a relationship had already been created between the tables, the Relationship window would recognize it and create a link between them:

If a necessary relationship doesn't show between two tables, to create one between a primary key of a table and its corresponding foreign key in the other table, drag the primary key column and drop it on the foreign key of the other table. The Edit Relationship dialog box would come up. You can examine it to make sure that the right columns have been selected:

If the columns are the valid ones, you can click OK. If they are not, you can click the arrow of each combo box in the lists and select the desired columns, then click OK. After creating a relationship, to examine, configure, or change it, you can double-click the line that represents a link between two tables, or you can right-click that line and click Edit Relationship.

 

Practical Learning Practical Learning: Using the Relationships Window
  1. On the Database toolbar, click the Relationships button
  2. Right-click the window and click Show Table...
  3. Observe the names of table. Any table that is not displaying, click it in the Show Table dialog box and click Add. After adding all the tables, click Close
  4. Click AccountTypeID from the AccountTypes list. Drag it and drop it on top of AccountTypeID of the Customers list
     
    Creating a Relationship
  5. In the Edit Relationship dialog box, click Create
  6. In the same way, create a relationship between the DepositTypeID field of the DepositTypes list and the DepositTypeID field of the Transactions list:
     
  7. Close the Relationships window
  8. If asked whether you want to save it, click Yes
  9. In the Database window, click Forms and double-click Customers
  10. Switch it to Design View
  11. On the form, click the AccountTypeID text box and press Delete
  12. In the Toolbox, make sure the Control Wizards button is down. Click Combo Box and click the area where the AccountTypeID text box was
  13. In the first page of the wizard, accept the first radio button and click Next
  14. In the second page of the wizard, click AccountTypes and click Next
  15. In the third page of the wizard, double-click AccountType and click Next three times
  16. In the fifth page, select AccountTypeID in the combo box and click Next
  17. Set the label to Account Type and click Finish
  18. Change the new combo box' Name to AccountTypeID
  19. Complete the design the form as follows:
     
  20. Save the form and switch it to Form View
  21. Se the account types as follows:
     
    Account Number Account Type
    28-3782-85 Checking
    92-3782-48 Checking
    38-4227-52 Checking
    68-6434-50 Checking
    83-4654-27 Saving
    47-4783-92 Checking
    82-3763-24 Checking
    72-3474-22 Checking
    34-5458-48 Checking
    29-4586-64 Saving
    68-3465-85 Checking
    40-4658-26 Checking
    56-8468-15 Checking
    94-7785-37 Checking
    37-5764-80 Checking
    34-9754-27 Certificate of Deposit
    72-9375-44 Checking
    37-5490-16 Checking
    20-3454-96 Saving
    76-5475-24 Checking
    27-3457-48 Checking
  22. Close the form
 

Relationships and Data Integrity

As mentioned in previous sections, relationships allow information to flow from one list, the parent table, to another list, the child table. When maintaining records, sometimes a piece of information may become obsolete. An employee may decide to change or to delete data from the parent table. This would cause the record in the child table to become orphan. When this happens, you need to take appropriate actions. Referential integrity is the ability to take care of necessary details when data from a table gets changed or deleted.

When a piece of information is changed in a parent table, you need to make sure that the change is replicated to the related child table. If you are creating or troubleshooting a relationship in the Relationships window, after displaying the Edit Relationship dialog box for a particular relationship, you can click the Enforce Referential Integrity check box. This makes available two other check boxes: Cascade Update Related Fields and Cascade Delete Related Fields.
 

Practical Learning Practical Learning: Insuring Referential Integrity
  1. On the main menu, click Tools -> Relationships...
  2. In the Relationships window, double-click the line between AccountTypes and Customers
  3. In the Edit Relationship dialog box, click the Enforce Referential Integrity check box
  4. Click the Cascade Update Related Fields and the Cascade Delete Related check boxes
     
  5. Click OK
  6. Right-click the line between Customers and Transactions and click Edit Relationship...
  7. In the Edit Relationship dialog box, click the Enforce Referential Integrity check box
  8. Click the Cascade Update Related Fields and the Cascade Delete Related check boxes
  9. In the same way, enforce the referential integrity of the other relationships
     
  10. Close the Relationships window.
    If asked whether you want to save it, click Yes
  11. In the Database window, click Tables and click Transactions
  12. On the Database toolbar, click the New Object: AutoForm button
  13. Save the form with the default name
  14. Switch the form to Design View and change its design as follows:
     
  15. Save the form
  16. On the form, double-click the TransactionTypeID combo box
  17. In the All tab of the Properties window, double-click On Change and click its ellipsis button
  18. Implement the event as follows:
      
    Private Sub TransactionTypeID_Change()
    On Error GoTo TransactionTypeID_Error
    
    If [TransactionTypeID] = 1 Then ' Deposit
        [DepositAmount].Enabled = True
        [DepositTypeID].Enabled = True
        [WithdrawalAmount].Enabled = False
        [WithdrawalTypeID].Enabled = False
        [ServiceCharge].Enabled = False
        [ChargeReasonID].Enabled = False
    ElseIf [TransactionTypeID] = 2 Then ' Withdrawal
        [DepositAmount].Enabled = False
        [DepositTypeID].Enabled = False
        [WithdrawalAmount].Enabled = True
        [WithdrawalTypeID].Enabled = True
        [ServiceCharge].Enabled = False
        [ChargeReasonID].Enabled = False
    ElseIf [TransactionTypeID] = 5 Then ' Service Charge
        [DepositAmount].Enabled = False
        [DepositTypeID].Enabled = False
        [WithdrawalAmount].Enabled = False
        [WithdrawalTypeID].Enabled = False
        [ServiceCharge].Enabled = True
        [ChargeReasonID].Enabled = True
    Else
        [DepositAmount].Enabled = True
        [DepositTypeID].Enabled = True
        [WithdrawalAmount].Enabled = True
        [WithdrawalTypeID].Enabled = True
        [ServiceCharge].Enabled = True
        [ChargeReasonID].Enabled = True
    End If
    
    Exit Sub
TransactionTypeID_Error:
    MsgBox "There is a problem with processing." & vbCrLf & _
           "Please call Customer Service"
    Resume Next
End Sub
  19. Return to the form and, while the TransactionTypeID combo box is still selected, in the All tab of the Properties window, double click On Lost Focus
  20. Click its ellipsis button and implement the event as follows:
      
    Private Sub TransactionTypeID_LostFocus()
    TransactionTypeID_Change
End Sub
  21. Click the button at the intersection of the both rulers
  22. In the All tab of the Properties window, double-click On Current and click its ellipsis button
  23. Implement the event as follows:
      
    Private Sub Form_Current()
    TransactionTypeID_Change
End Sub
  24. Save the form and switch it to Form View
  25. Create a few transactions
  26. Click Close

 
 
Previous Copyright © 2005-2016, FunctionX Next