Lesson 5 - Adding Relationships

Top  Previous  Next

Overview

Table relationships describe how tables are related based upon common keys, columns within those keys, and how referential integrity should be maintained. Defining table relationships is generally the last step in the creation of a dictionary because columns and keys must be defined in order to create table relationships.

Relationships for a table are defined by highlighting from the Relations list box and pressing the Add button when the appropriate table is active. You can also add relationships via the Entity Browser:

 

 

Let’s explore the Relationship Properties options in more detail:

 

Adding Relationships

Type

The type of relationship being created is specified using this combo box: 1:Many, or Many:1.

Primary Key

The key in the table on the one side of a one-to-many relationship containing the common column(s). Notice the down arrow appearing next to this column. This symbol indicates that a drop list is available that will present you with a list of valid key name choices.

Related Table

This is the other table with which you are creating the relationship. If it is one the ONE side then it will be titled PARENT, and if it is on the MANY side it will be titled CHILD. A list box is also available for this column to give you a list of tables currently defined.

Foreign Key

The key in the table on the many side of a one-to-many relationship that contains the common column(s).

Column Mapping

Presents a list of the columns contained within the Access Keys that were specified for both tables. The columns in each key must be matched with the common columns in the corresponding table.

Map by Name allows the common columns to be matched automatically if the column names are the same.

Map by Order does just that regardless of column names.

The third method of mapping is to double click on [No Link] and then pick a suitable column from the other table. You will find it necessary to use this third method if a one-way relationship exists between the two tables, meaning that a suitable key exists in only one of the tables.

Referential Integrity Constraints

Allows "rules" to be defined for the maintenance of table relationships:

On Update

Update attributes allow you to specify what action should be taken (if any) when an attempt is made to modify a parent record with related child records.

On Delete

Delete attributes allow you to specify what action should be taken when an attempt is made to delete a parent record that has child records related to it.

After creating tables, columns, keys, memos, and table relationships, the dictionary is near complete, and an application may be designed that uses the data dictionary. Let’s look at a few more options that further demonstrate the power of the Dictionary Editor.

 

Next: Lesson 6 - Trigger Properties