A New Look at Relations Using REFS

Relations in Forms are by far one of the best methods of creating and preserving data relationships between database tables, thereby maintaining data integrity. This concept of relations in forms has been extended to accommodate object tables and relational tables having object columns (henceforward referred to as object-relational tables).

A master-detail relation can be created to link:

• object tables
• object tables and object-relational tables
• object-relational tables; that is, to columns based on object types

A master-detail relation linking any of the preceding can be created by

• Using REF columns
• Using a join condition

Using Join Conditions for Forms Relations

This technique is similar to joining two relational tables having a primary-key (PK) and foreign-key (FK) relationship between them. Using join conditions for Forms relations is still reserved to preserve such PK-FK foreign key relationships.

The join condition for our ADDRESS_PERSON relation would look like

address.street = person.off_add_street AND address.city = person.off_add_city AND address.state = person.off_add_state AND address.zip = person.off_add_zip

Using REF Items for Forms Relations

This subsection explains how to create a master-detail relation between blocks using REF items. First we tell what a REF item is and then explain how to create a relation using it.

The object table ADDRESS and the object-relational table PERSON ”even though not " related " by means of a foreign key at the server end ”are linked by a REF reference, and can be linked in forms using a master-detail relationship.

REF items provide a means of linking object and/or object-relational tables. This means the query and DML work behind the scenes as they do for a relation involving two relational tables.

You should always create a relation based on REFS while linking object and/or object relational tables.

A REF reference is required between two objects and/or object-relational tables for the following reasons:

• The scope of a nested object is limited to the context of the parent object.
• Thus, each row object (in case of an object table) and column object (in case of an object-relational table) cannot be referenced outside of the object table.
• Each row of an object table will hold its own PRIVATE instance of the row or column object and thus will have no accessibility to a second object table.

In the example form,

• The relation name is ADDRESS_PERSON.
• The master block is ADDRESS.
• The detail block is PERSON.

Tip

Select the REF column(s) while creating the detail block. All the REF columns have to be selected.

Figure 10.1 shows a sample screen while creating the PERSON table that has two REF columns OFF_ADD and HOME_ADD.

Figure 10.1. Creation of a Data Block with REF items.

The column OFF_ADD (highlighted in the figure) is the actual REF column from the PERSON table. This column holds a pointer to a row in the ADDRESS object table and Forms generates a unique Object Identifier for it that acts as an Object FK to the ADDRESS table.

You can see that the OFF_ADD is named OFF_ADD (Lookup). This is look up related and is generated automatically by Forms to populate an LOV for the OFF_ADD item.

The relation can be created in two ways. First, you can create the relation when you create the PERSON block. This is done by clicking the Create Relationships button and checking the Auto-join Data Blocks check box. Forms prompts for a choice of master blocks if there are multiple REF items in the PERSON block. This is shown in Figure 10.2.

Figure 10.2. List of master blocks in case of multiple REF items.

The relation is created with the following special properties: Relation Type is set to Ref, and Detail Reference Item is set to the Ref item corresponding to the REF column in the base object table (in this case, OFF_ADD ).

The following properties are set for the master and detail blocks:

• The Master block has the Include REF Item property set to Yes.
• Set the DML Returning Value property for the Master block. You have to set this explicitly after creating the relation.
• Both the Master and Detail blocks have the Alias properties set to the first letter of the respective block names .

Second, you can explicitly create a relation using the Relations node of the Object Navigator. The preceding properties for a relation have to be set in this case.

Tip

Always create one relation for each REF item included in the detail block.

The Master block should have the DML Returning Value property set to Yes. This has to be set to propagate the new OID generated each time a DML operation is performed on the same record ”for example, an UPDATE operation. This OID is the referred to OID: referred to by the detail block.

If the master block is an object table, the property Include REF item should be set to Yes.

References to an object are made by using the REF operator with the table alias passed as an input parameter. The row of the child object table is located by comparing this OID, returned by REF, to the REF column in the child table. ROWID rather than a primary key value is used to locate the correct OID corresponding to the block record.

This is shown as follows :

........ ........ CURSOR person_cur IS SELECT 1 FROM person p WHERE p.off_add = (SELECT REF(a) FROM address a WHERE rowid = :address.rowid); ........ ........