Working with Foreign Keys

OracleAS TopLink uses references to maintain foreign key information. OracleAS TopLink defines the reference as a property of the table containing the foreign key. This may or may not correspond to an actual constraint that exists on the database.

If you import tables from the database, OracleAS TopLink creates references that correspond to existing database constraints (if the driver supports this). You can also define any number of references in the OracleAS TopLink Mapping Workbench without creating similar constraints on the database.

OracleAS TopLink uses these references when defining relationship mappings and descriptors' multiple table associations.

Understanding Foreign Keys

A foreign key is a combination of columns that reference a unique key, usually the primary key, in another table. Foreign keys can be any number of fields (similar to a primary key), all of which are treated as a unit. A foreign key and the parent key it references must have the same number and type of fields.

Relationship mappings use foreign keys to find information in the database so that the target object(s) can be instantiated. For example, if every Employee has an attribute address that contains an instance of Address (which has its own descriptor and table) then, the one-to-one mapping for the address attribute would specify foreign key information to find an address for a particular Employee.

OracleAS TopLink classifies foreign keys into two categories in mappings — foreign keys and target foreign keys:

• In a foreign key, the key is found in the table associated with the mapping's own descriptor. In the previous example, a foreign key to ADDRESS would be in the EMPLOYEE table.

• In a target foreign key, the reference is from the target object's table back to the key from the mapping's descriptor's table. In the previous example, the ADDRESS table would have a foreign key to EMPLOYEE.

  
  

  Caution: Make sure you fully understand the distinction between foreign key and target foreign key before defining a mapping.

  
  

Specifying Foreign Keys

If you import tables from the database, OracleAS TopLink creates references that correspond to existing database constraints (if supported by the driver). You can also define references in OracleAS TopLink without creating similar constraints on the database.

To display existing references for a table, use the References tab (see Figure 3–5). References that contain the On Database option will create a constraint that corresponds to the references.

Note: Your database driver must support this.

To create a foreign key:

1. Choose a database table in the Navigator pane that will contain the foreign key.

2. Click the References tab in the Editor pane (see Figure 3–5).

3. Select a reference table. See "Creating Table References" on page 3-6 for more information.

4. Add a key pair for the reference. See "Creating Field References" on page 3-7 for more information.

   Use the Source Field and Target Field drop-down lists to choose the appropriate fields on the source and target tables.

   Repeat step 4 for each foreign key field.