Use to create and configure the references of your datastores.
A reference is a functional link between two datastores. It corresponds to a foreign key in a relational model. For example: The INVOICE datastore references the CUSTOMER datastore through the customer number.
The Reference Editor has the following tabs:
Columns (for user or database references)
Expression (for complex user references)
Behavior (for database references)
| Properties | Description |
|---|---|
| Name | Reference name. |
| Type |
|
| Model | Model of the referenced table, or the primary key table, or the table known as parent. |
| Table | Referenced table, or the primary key table, or the table known as parent. |
| Active on the database | Reference on the database that contains the datastore. |
Use to view and define user or database references.
For user or database references, a list of the correspondence between the foreign key table columns (child table) and the primary key table (parent or referenced table) must be defined. In accordance with the ISO standard for relational models, only the rows in the foreign key table that include all the non-null foreign key columns are checked.
This tab is only displayed for user or database references.
Use to view and define complex user references.
The criterion for connecting two tables may be complex. In this case, a free expression may be entered in this tab.
Use the Expression Editor to define the free expression. Click Launch the Expression Editor to open it.
Verify your expression by clicking Testing query on the DMBS.
This tab is only displayed for complex user references.
Use to view indicative metadata information.
This information shows what action is launched by the data server if a row is deleted from the primary key table, or if the primary key value for a row is modified in the primary key table.
This tab is only displayed for database references.
Use to display and configure the quality control properties.
The quality control properties are taken into account during a flow control or during a static control.
This section shows the type of quality control for which this reference will be checked.
| Properties | Description |
|---|---|
| Flow | During a flow control for this datastore, the reference is verified if this check box is selected. This information is a default value that can be modified when the mapping is designed. It is recommended to activate this type of control to ensure data quality, even if the target technology also controls the information. |
| Static | During a static control, that is, a quality control of data already present in this datastore, the reference is verified if the box is selected. |
This section shows the quality control statistics.
| Properties | Description |
|---|---|
| Number of rows in the table (Without Filter) | Number of rows in the table with no filtering. |
| Number of rows checked in the table | Number of rows with filter on this datastore. These rows are checked for the reference. |
| Number of Correct References (on not null columns) | Number of rows with a match in the referenced table. |
| Number of References with errors | Number of rows with no match in the referenced table. |
Click Check to retrieve the number of records that respect or do not respect this constraint. To obtain a trace of the rows in error, a static control must be launched from either the Datastore or the Model Editor.
"Adding and Deleting Constraints and Filters" in the "Creating and Using Data Models and Datastores" chapter in Oracle Fusion Middleware Developing Integration Projects with Oracle Data Integrator