Implied Foreign Keys

Implied foreign keys are dependencies that exist between tables but are not defined in the database. In a data warehouse environment, it is a common practice not to create foreign keys. However, it becomes necessary to show these dependencies for presentation or reporting purposes.

In SQL Developer Web, you can display implied foreign keys for objects in a star schema by defining them or by discovering them in the data dictionary. It is possible to have more than one source for implied foreign keys.

Define Implied Foreign Keys

You can defined implied foreign keys in two ways:

• Using the Implied Foreign Keys dialog

  You can define implied foreign keys using the Implied Foreign Keys dialog.

  1. Right-click an object (table or view) in a diagram and select Implied Foreign Keys. The Implied Foreign Keys dialog is displayed.

  2. In the Implied Foreign Keys dialog, click the + icon to add an entry in the grid.

  3. Select the entry in the grid to enable and enter values in the following fields:

     • Referenced Object: Object in the diagram that has a dependency to the source object.

     • Local Column: Name of the column in the source object.

     • Referenced Column: Name of the column in the targeted object.

     • Discovery Sources: Automatically prefilled, displays whether the implied foreign keys have been defined or were discovered in the data dictionary.

  4. Click OK. The implied foreign key dependency is displayed with a dotted line on the diagram.

• By dragging the arrow to the referenced object in the diagram

  You can also define an implied foreign key in the following way:

  1. Select the source object on the diagram.

  2. Click and drag the small blue curved arrow to the referenced object. The dependency will be displayed with a dotted line on the diagram.

  3. Right-click the source object and enter the column names in the Implied Foreign Keys dialog.

Discover Implied Foreign Keys in the Data Dictionary

SQL Developer Web can automatically create a star schema by searching for several types of definitions in the data dictionary.

In the Navigator tab, right-click an object (table or view) and select Add Object as Star Schema to Diagram. The object must be a fact table. The data dictionary is then searched for joins and dependencies related to the object, such as:

• Foreign keys defined for the selected table to other tables. If implied foreign keys are later discovered for the same columns, they will not be displayed on the diagram.

• Joins used in the definitions of bitmap join indexes, materialized views with aggregates, and attribute clustering.

• Dependencies based on dimension definitions and column name matching in fact table.

• Fact and dimension definitions for Analytic views and OLAP cube and dimensions.