| **Navigation:**  [[welcome to my product .htm|User's Guide and Tutorials]] > Guided Tour of Working in Clarion >====== 4 - Adding Keys and Relations ====== | [[lc adding tables and columns.htm|{{btn_prev_n.gif|Previous page}}]][[welcome to my product .htm|{{btn_home_n.gif|Return to chapter overview}}]][[lc importing existing data.htm|{{btn_next_n.gif|Next page}}]] |
|  ||

** **

Now that all the tables are defined, we can add keys then specify the table relationships. You already have defined the keys for the two tables you created in the //GSLesson// application in the Getting Started lesson. In this chapter, we'll define keys for the remaining tables.

**Starting Point:**

**The LCLESSON.DCT should be open.**

**Examining the Keys for the Orders Table**

The columns in the Orders table that relate to other tables in the database are the OrderNumber and CustNumber columns.

{{lesson4-1.jpg|Lesson4-1.jpg}}

·The OrderNumber column relates to the Detail table.

There should be no duplicate or null order numbers in the Orders table; this is a //primary// key.

There may be multiple Detail rows for a single matching Order Number. Therefore, this is a //One to Many// relationship'the Orders table is the "Parent" of the Detail table.

·The CustNumber column relates to the Customer table.

There will be duplicate values in the CustNumber column that relate to rows in the Customers table. The key we define in the Orders table is a //foreign// key. The Customers table key does not allow duplicates and nulls, and was defined as the primary key for that table.

Multiple Order rows can exist for each Customer, making this a //Many to One// relationship'the Orders table is the "Child" of the Customers table.

The Orders table was imported in the Getting Started lesson, and there are two keys already defined that satisfy the above requirements.

**Defining Keys for the Detail Table**

The columns in the Detail table that relate to other tables in the database are the ProdNumber and OrderNumber columns.

{{gslesson4-2.jpg|GSLesson4-2.jpg}}

·The OrderNumber column relates to the Orders table.

There will be duplicate values in the OrderNumber column that relate to rows in the Orders table. The key we define in the Detail table is another //foreign// key. The Orders table key does not allow duplicates and nulls, and was defined as a primary key.

There may be more than one Detail row for a single matching Order Number. Therefore, this is a //Many to One// relationship, with the Detail table the "Child" of the Orders table.

·The ProdNumber column relates to the Products table.

There will be duplicate values in the ProdNumber column for the rows in the Detail table. There may be more than one Detail row containing a single Product Number. Therefore, this is another //Many to One// relationship, with the Detail table the "Child" of the Product table.

** **

**Define the First Foreign Key**

Define //ProdNumberKey// so that there may be duplicate //ProdNumber// values in this table.

1.Highlight the //Detail// table in the **DCT Explorer** list.

2.In the //Keys// view, press the **Add** button.

{{addkey.jpg|AddKey.jpg}}

3.Type //ProdNumberKey// in the **Label** entry.

4.Select the **Columns** tab.

5.Press the **Add** button.

6.Select //ProdNumber// then press the **Select** button.

7.Press the **Cancel** button in the //Select a Column// dialog, and then press **OK** to close the //Key Properties// dialog.

A blank //Key Properties// dialog appears, ready for you to specify another key.

**Define the Second Foreign Key**

1.Type //OrderNumberKey// in the **Label** entry.

2.Select the **Columns** tab.

3.Press the **Add** button.

4.Select //OrderNumber// then press the **Select** button.

5.Press the **Cancel** button in the //Select a Column// dialog.

6.Press the OK button to close the //Key Properties// dialog.

7.Press the **Cancel** button to close the blank //Key Properties// dialog.

8.Choose **File **{{blttria.jpg|BLTTRIA.jpg}}** Save**, or press the **Save** button {{savebutton.jpg|savebutton.jpg}} on the tool bar.

**Defining Keys for the Products Table**

Only one column in the Products table relates to another table in the database: the ProdNumber column.

·The ProdNumber column relates to the Detail table.

{{gslesson4-3.jpg|GSLesson4-3.jpg}}

There should be no duplicate or null order numbers in the Products table; this is a //primary// key.

For each ProdNumber in the row there can be many Detail rows. This is a //One to Many// relationship with the Products table a "Parent" to the Detail table.

**Create the Primary Key**

__**Name the Key**__

1.Highlight the //Products// table in the **DCT Explorer** list.

2.In the //Keys// view, press the **Add** {{addtable.jpg|AddTable.jpg}} button.

3.Type //ProdNumberKey// in the **Label** entry.

4.In the Attributes group, check the **Require Unique Value** box, then check the **Primary Key** box.

5.Check the **Auto Number** box.

6.Select the **Columns** tab.

7.Press the **Add** button.

8.In the //Select a Column// window, select //ProdNumber// then press the **Select** button.

9.Press the **Cancel** button to close the //Select a Column// window.

10.Press the **OK** button to close the //Key Properties// dialog.

A blank //Key Properties// dialog appears, ready for you to specify another key.

**Define an Alphabetical Key**

Users will probably want to see the list of Products in alphabetical order, so we'll add a key for that.

1.Type //ProdDescKey// in the **Label** entry.

2.Select the **Columns** tab.

3.Press the **Add** button.

4.Select //ProdDesc// then press the **Select** button.

5.Press the **Cancel** button to close the //Select a Column// window.

6.Press the **OK** button to close the //Key Properties// dialog.

7.Press the **Cancel** button to close the blank Key Properties dialog.

8.Choose **File** {{blttria.jpg|BLTTRIA.jpg}} **Save**, or press the **Save** button {{savebutton.jpg|savebutton.jpg}} on the tool bar.

**Defining a Key for the Phones Table**

The column in the Phones table that relate to the Customer table in the database is the CustNumber column.

{{gslesson4-4.jpg|GSLesson4-4.jpg}}

·The CustNumber column relates to the Customer table.

There will be duplicate values in the CustNumber column that relate to rows in the Customer table. The key we define in the Phones table is another //foreign// key. The Customer table key does not allow duplicates and nulls, and was defined as a primary key.

There may be more than one Phones row for a single matching Customer Number. Therefore, this is a //Many to One// relationship, with the Phones table the "Child" of the Customer table.

**Define the Foreign Key**

Define //CustNumberKey// so that there may be duplicate //CustNumber// values in this table.

1.Highlight the //Phones// table in the **DCT Explorer** list.

2.In the //Keys// view, press the **Add** {{addtable.jpg|AddTable.jpg}} button.

3.Type //CustNumberKey// in the **Label** entry.

4.Select the **Columns** tab.

5.Press the **Add** button.

6.Select //CustNumber// then press the **Select** button.

7.Press the **Cancel** button to close the //Select a Column// window.

8.Press the **OK** button to close the //Key Properties// dialog.

9.Press the **Cancel** button to close the blank //Key Properties// dialog.

10.Choose **File **{{blttria.jpg|BLTTRIA.jpg}}** Save**, or press the **Save** button {{savebutton.jpg|savebutton.jpg}} on the tool bar.

**Defining Table Relationships**

{{notebox.jpg|NoteBox.jpg}}

The relationships for the //Customer//, //Orders//, and //States// tables were defined in the Getting Started lessons. Refer back to that section in the Getting Started if you would like to review those definitions.

**Defining Relationships for the Phones Table**

Now that all the keys are defined, we can add the relations. Once you have defined relationships, you can add Validity Checks for the columns that should only contain values that exist in another table. These are the last steps to completing the data dictionary.

·CustNumberKey relates the Phones table to the Customer table in a //Many to One// relationship.

__**Define the relationship**__

1.Highlight the //Phones// table in the **DCT Explorer** if not already highlighted.

2.In the lower right pane, press the **Add **button {{addtable.jpg|AddTable.jpg}} located just above the **Relations** list. The //Relationship Properties// dialog appears:

{{relpropslc.jpg|RelPropsLC.jpg}}

3.Choose //MANY:1// from the **Type** dropdown list.

Notice that, when you chose MANY:1, the prompts for the Primary Key and Foreign Key columns switched places. This happens because we are now defining this relationship from the "Child" table's viewpoint; the opposite side of the relationship to what we did previously. A Primary Key is always in the Parent table, while a Foreign Key is always in the Child table.

4.Choose //Pho:CustNumberKey// from the **Foreign Key** dropdown list.

5.Choose //Customer// from the **Related Table** dropdown list.

This establishes the Customer table as the "Parent" in this relationship.

6.Choose CUS://KeyCustNumber// from the **Primary Key** dropdown list.

7.Press the **Map by Name** button.

__**Set up the Referential Integrity constraints**__

1.Choose //Cascade// from the **On Update** dropdown list.

Although we are defining this relationship from the "Child" table's viewpoint, the Referential Integrity constraints are still set on the "Parent" table actions.

2.Choose //Cascade// from the **On Delete** dropdown list.

3.Press the **OK** button.

4.Press the **Cancel** button to close the new //Relationship Properties// window.

5.Choose **File** {{blttria.jpg|BLTTRIA.jpg}} **Save**, or press the **Save** button {{savebutton.jpg|savebutton.jpg}} on the tool bar.

**Defining Relationships for the Detail Table**

Each time you define a relationship in the Dictionary Editor, you define it for both tables at the same time.

The relationships for the Detail table:

·//ProdNumberKey// relates the Detail table to the Products table in a Many to One relationship.

·//OrderNumberKey// relates the Orders table to the Detail table in a One to Many relationship. From the Detail table, you can also look at it as "Many detail records relate to a single Order record".

__**Define the Detail-Product relationship**__

1.Highlight the //Detail// table in the **DCT Explorer** list.

2.In the lower right pane, press the **Add **button {{addtable.jpg|AddTable.jpg}} located just above the **Relations** list. The //Relationship Properties// dialog appears:

3.Choose //MANY:1// from the **Type** dropdown list.

4.Choose //DTL:ProdNumberKey// from the **Foreign Key** dropdown list.

5.Choose //Products// from the **Related Table** dropdown list.

6.Choose //PRD:ProdNumberKey// from the **Primary Key** dropdown list.

7.Press the **Map by Name** button.

__**Set up the Referential Integrity constraints**__

1.Choose //Restrict// from the On Update dropdown list.

We won't allow any changes to the product numbers.

2.Choose //Restrict// from the On Delete dropdown list.

3.Press the **OK** button.

4.Press the **Cancel** button to close the new //Relationship Properties// window.

5.Choose **File** {{blttria.jpg|BLTTRIA.jpg}} **Save**, or press the **Save** button {{savebutton.jpg|savebutton.jpg}} on the tool bar.

__**Define the Detail-Order relationship**__

1.Highlight the //Detail// table in the **Tables** list.

2.In the lower right pane, press the **Add **button {{addtable.jpg|AddTable.jpg}} located just above the **Relations** list. The //Relationship Properties// dialog appears:

3.Choose //MANY:1// from the **Type** dropdown list.

4.Choose //DTL:OrderNumberKey// from the **Foreign Key** dropdown list.

5.Choose //Orders// from the **Related Table** dropdown list.

6.Choose //ORD:KeyOrderNumber// from the **Primary Key** dropdown list.

7.Press the **Map by Name** button.

__**Set up the Referential Integrity constraints**__

1.Choose //Restrict// from the On Update dropdown list.

2.We won't allow any changes to the product numbers.

3.Choose //Restrict// from the On Delete dropdown list.

4.Press the **OK** button.

5.Press the **Cancel** button to close the new //Relationship Properties// window.

6.Choose **File** {{blttria.jpg|BLTTRIA.jpg}} **Save**, or press the **Save** button {{savebutton.jpg|savebutton.jpg}} on the tool bar.

**Defining Relationship-Dependent Validity Checks**

Now that all the table relationships are defined, we can set the Validity Checks for two columns that we expect to use on update forms.

·When entering a new Orders row, we can specify that the CustNumber must match an existing row in the Customer table.

·When entering a new Detail row, we can specify that the ProdNumber must match an existing row in the Products table.

**Define the Validity Check for Order Rows**

1.Highlight the //Orders// table in the **DCT Explorer** list.

2.In the **Fields** //Quick View//, highlight //CustNumber// and press the **Change** {{propsbut.jpg|propsbut.jpg}} button.

3.Select the **Validity Checks** tab.

4.Select the **Must Be In Table** radio button.

5.Choose //Customer// from the **Table Label** dropdown list. Actually, the IDE detects that it is the only valid table here and selects it for you.

This requires that the column can only contain values verified by getting a matching row from the Customer table. This is validated using the table relationship information, which is why this Validity Check cannot be set until the relationships have been defined.

6.Press the **Save and Close **button in the Entity Browser to close the Orders table.

**Define the Validity Check for Detail Rows**

1.Highlight the //Detail// table in the **DCT Explorer** list.

2.In the **Fields** //Quick View//, highlight //ProdNumber// and press the **Change** {{propsbut.jpg|propsbut.jpg}} button.

3.Select the **Validity Checks** tab.

4.Select the **Must Be In Table** radio button.

5.Choose //Products// from the **Table Label** dropdown list. Again, this is auto-selected for you.

6.Press the **Save and Close **button in the Entity Browser to close the Orders table.

7.Press the **Save** button to save your work so far.

**OK, What Did I Just Do?**

Here's a quick recap of what you just accomplished:

| {{blueck.jpg|blueck.jpg}} | You created the keys for all the new table definitions. |
| {{blueck.jpg|blueck.jpg}} | You defined the relationships between the new tables. |
| {{blueck.jpg|blueck.jpg}} | You defined two relationship-dependent validity checks to require that foreign key column values always have related primary key rows in a parent table. |

The data dictionary is now complete to this point. In the next chapter, we will import some existing data from another application, to show you just how simple it is to accomplish.

[[lc importing existing data.htm|Click here to jump to the next lesson]]