Relationships between objects in typed tables

You can define relationships between objects in one typed table and objects in another table. You can also define relationships between objects in the same typed table.

For example, assume that you defined a typed table that contains instances of departments. Instead of maintaining department numbers in the Employee table, the Dept column of the Employee table can contain a logical pointer to one of the departments in the BusinessUnit table. These pointers are called references, and are illustrated in Figure 1.

This diagram shows the table Employee_t Table with rows. — Figure 1. Structured type references from Employee_t to BusinessUnit_t

A normal table (a table that is not a typed table) can have a REF column that refers to a typed table. However, a typed table cannot have a REF column that points to a normal table.

Important: References do not perform the same function as referential constraints. It is possible to have a reference to a department that does not exist. If it is important to maintain integrity between department and employees, you can define a referential constraint between those two tables. The real power of references is that it gives you the ability to write queries that navigate the relationship between the tables. What the query does is dereference the relationship and instantiate the object that is being pointed to. The operator that you use to perform this action is called the dereference operator, which looks like this: ->.

For example, the following query on the Employee table uses the dereference operator to tell Db2® to follow the path from the Dept column to the BusinessUnit table. The dereference operator returns the value of the Name column:

   SELECT Name, Salary, Dept->Name
      FROM Employee;