The Relationship Editor lists the pairs of corresponding parent table and child table entries that make up the relationship.
Define or edit relationships by:
Although the rules for creating Optim™ relationships are more flexible than those for creating database-defined relationships, there are some restrictions:
In a Relationship definition for a multi-byte or Unicode database:
Parent | Supported/Not Supported | Child | Description |
---|---|---|---|
CHAR | ![]() |
CHAR | Supported, semantics must match |
NCHAR | ![]() |
NCHAR | Supported, semantics irrelevant. |
CHAR | ![]() |
NCHAR | Not Supported |
CHAR | ![]() |
VARCHAR | Supported, semantics must match. |
NCHAR | ![]() |
NVARCHAR | Supported, semantics irrelevant. |
CHAR||NCHAR | ![]() |
NCHAR||CHAR | Supported, if character semantics; not supported if byte semantics. |
CHAR||NCHAR | ![]() |
NCHAR||NCHAR | Supported, if character semantics; not supported if byte semantics. |
Violating these restrictions causes an error. For further details about column compatibility, see Compatibility Rules for Relationships.
Select Parent Columns or Child Columns from the Tools menu to add or replace column names. You can switch between the Parent Table Columns dialog and Child Table Columns dialog by selecting the appropriate command from the Tools menu in the Relationship Editor.
Fully qualified name of the parent table from which you can select names of columns to include in the relationship.
To select from the list of columns, drag column names from the dialog to the appropriate grid cell in the Relationship Editor. The inserted column name replaces any value in the current grid cell. The data types of the paired columns in the editor are evaluated and the Status is automatically updated.
Change the column display by selecting View menu commands in the Parent Table Columns dialog:
You can specify or modify a column name by typing over the name or using shortcut menu commands to Insert, Remove, or Remove All column names.
In addition, you can specify expressions, including substrings, concatenations, constants and literals for either the parent or child table column in a relationship. This flexibility is one of the most powerful features of Optim relationships. You can use any of the following values:
You can define a string literal containing any characters, for example: 'CA' or '90210'.
Use the Substring Function to select part of a column value for a relationship. The Substring Function returns a substring of the contents of the named column. The Substring Function format is:
SUBSTR(columnname, start, [length])
Concatenation allows you to combine column values or combine a column value with another value, using a concatenation operator (CONCAT, ||, or +). A concatenated expression can include character or binary values, but not both:
You can define relationships using different data structures. For example, the data in two or more columns in one table may correspond to data in a single column in another table. You can define this type of relationship using concatenation or the Substring Function.
Assume that an address in the CUSTOMERS table is in two columns, ADDRESS1 and ADDRESS2, and in one column, ADDRESS, in the SHIP_TO table. You can define a relationship between the two tables by concatenating the columns in the CUSTOMERS table:
CUSTOMERS Table | SHIP_TO Table |
---|---|
ADDRESS1 || ADDRESS2 | ADDRESS |
ADDRESS1 CONCAT ADDRESS2 | ADDRESS |
ADDRESS1 + ADDRESS2 | ADDRESS |
You can define the same relationship as in the prior example, using substrings:
CUSTOMERS Table | SHIP_TO Table |
---|---|
ADDRESS1 | SUBSTR(ADDRESS,1,25) |
ADDRESS2 | SUBSTR(ADDRESS,26,25) |
To compare the results of using a concatenation operator or the Substring Function, examine the generated SQL in each case.
SQL for a relationship using the concatenation operator:
SELECT * FROM TABLE2 WHERE TABLE2.ADDRESS = 'composite value from both parent columns'
SQL for the relationship defined using the Substring Function:
SELECT * FROM TABLE2 WHERE SUBSTR(TABLE2.ADDRESS, 1,25)='value from parent ADDRESS1 column' AND SUBSTR(TABLE2.ADDRESS,26,25)='value from parent ADDRESS2 column'
Complex SQL is generated in the substring example; the DBMS must scan all rows in the table to achieve the desired result and may not use an index, even if one exists. In general, concatenation provides better performance.
A data-driven relationship exists when a row in the parent table is related to rows in one of several child tables on the basis of the value in a particular column. You can define a data-driven relationship using:
For example, consider a pair of sample tables used to determine employee insurance rates for males and females. The EMPLOYEE table stores the identification, age, and gender details for each employee:
EMPLOYEE_ID | AGE | GENDER |
---|---|---|
058-44-2244 | 38 | F |
106-46-0620 | 40 | M |
248-91-2890 | 27 | M |
The FEMALE_RATES table contains insurance rates for women based on age; the MALE_RATES table contains insurance rates for men based on age:
AGE | RATE |
---|---|
38 | .25 |
39 | .33 |
40 | .43 |
AGE | RATE |
---|---|
38 | .31 |
39 | .38 |
40 | .47 |
Two relationships are needed, one for rows in the EMPLOYEE table that contain data about female employees, and one for rows in the EMPLOYEE table that contain data about male employees.
Rows in the EMPLOYEE table are related to the FEMALE_RATES table when the value in the GENDER column matches the string literal 'F'. The AGE column in the EMPLOYEE table corresponds to the AGE column in the FEMALE_RATES table.
EMPLOYEE | FEMALE_RATES |
---|---|
GENDER | 'F' |
AGE | AGE |
In the second relationship, rows in the EMPLOYEE table are related to the MALE_RATES table. This relationship is identical to the first, except that the value in the GENDER column matches the string literal 'M'.
EMPLOYEE | MALE_RATES |
---|---|
GENDER | 'M' |
AGE | AGE |
For any row in the EMPLOYEE table, only one of the relationships can be satisfied because the column EMPLOYEE.GENDER must be 'M' or 'F'. After the appropriate relationship is selected, the related rows are identified by comparing values in the AGE columns.