User Tools

Site Tools


lc_adding_keys_and_relations.htm
Navigation:  User's Guide and Tutorials > Guided Tour of Working in Clarion >====== 4 - Adding Keys and Relations ====== Previous pageReturn to chapter overviewNext 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

·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

·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

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 Save, or press the Save button 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

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 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 Save, or press the Save button 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

·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 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 Save, or press the Save button savebutton.jpg on the tool bar.

Defining Table Relationships

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 located just above the Relations list. The Relationship Properties dialog appears:

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 Save, or press the Save button 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 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 Save, or press the Save button 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 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 Save, or press the Save button 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 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 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 You created the keys for all the new table definitions.
blueck.jpg You defined the relationships between the new tables.
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.

Click here to jump to the next lesson

lc_adding_keys_and_relations.htm.txt · Last modified: 2021/04/15 15:57 by 127.0.0.1