Creating Associations for JDBC Base Views Automatically¶

In the CREATE BASE VIEW section of JDBC data sources, when you select several tables/views and click Create selected, Design Studio will create a base view for each element. After that, the server will analyze if any of these tables have a primary key-foreign key constraints between them, in the source database. If there are, Design Studio will show a notification like the one below so you can automatically create associations that mirror these foreign key constraints.

If you already created the base views but not its associations, you can still do this same process of analyzing if any of these tables have a primary key-foreign key constraints between them. To do this, follow these steps:

1. Select the JDBC base views for which you want to create its associations. To select several views, in Server Explorer press the keyboard key Ctrl and click on each view, or press Shift to select a range of views. Then, on the bottom-left corner, click More > Discover associations.

2. Design Studio will display a dialog like the one below, with the associations that will be created. In this dialog you can:

   • Change the name of the new association.

   • If you do not want to create one of the associations, clear the check box next to its name.

3. Click Ok to create the associations (the selected ones).

Discover associations dialog¶

You can also discover the associations of a single JDBC base view by opening the view and clicking Associations. The tool will display a panel with the associations of the view. In this panel, click Discover.

The list of new associations of this dialog is based on the foreign key constraints the tables/views had in the database, at the time the base views were created. In the VQL of a base view, each parameter REFERENCES of the statement CREATE WRAPPER JDBC of the base view represents one of these constraints. If the foreign keys of the tables/views in the database changed since you created the base view, do a Source refresh of the base view to update this information.

To create an association involving a derived view or a non-JDBC base view, follow the steps of the section below.

Creating an Association Manually¶

To create a new association, click the menu File > New > Association.

The Tool will open the “View association” dialog and it will add the view that is currently selected in the Server Explorer. To add the other view of the association, click the Model tab and drag the view from the Server Explorer to this dialog.

The “View association” dialog has three tabs:

1. Model (see Creating a new association (“Model” tab)): tab where you have to drag the two views that will form the association and establish the mappings between the fields of the view. These views can belong to different databases.

   To link the field of a view with a field of another view, drag one field to the other. You will see a line between these fields. In views with many fields, you can right-click on the view and click Find to search the field you look for.

   To associate two fields, they must have the same type or compatible. For example, you can associate a float field with a double, an int with a long, etc.

   If you want to delete a view from the union, click on (in the top-right side of the view).

   The label on the top-left of each view – by default “(*)” – indicates the cardinality on that side of the association.

2. Properties (see Creating a new association (“Properties” tab)): in this tab you can do the following:

   1. Change the name of the association.

   2. Select/clear the check box Referential constraint. If selected, it indicates that this association represents a foreign key constraint. The section Referential Integrity in Associations explains in more detail the implications of selecting this check box.

   Note

   Only the associations marked as referential constraint are reported by the JDBC and the ODBC drivers as foreign keys.

   Note

   Virtual DataPort cannot enforce referential integrity of the data. The sources have to guarantee it.

   3. An association links two views and each view is one endpoint of the association. For each endpoint, do this:

      1. In the list next to the name of the view, select the multiplicity on that side of the association. The section Multiplicity of Associations below explains what the multiplicity of an association is.

      2. If “Referential constraint” is selected, in the next box, select Dependent if this side of the association represents a foreign key constraint. Otherwise, select Principal.

      3. Enter the role name of the end point and optionally, a description.

      4. Optionally, set a Role precondition. You can only do this on an end point when the other end point has a multiplicity of 0..1 or *. See more about this in the section Role Preconditions.

   4. In the “Model” tab you can only define mappings like “field X = field Y”. In this tab, you can define mappings that are more complex. To do this, click on to add a new mapping. The Tool will display a dialog where you specify the condition of the mapping. Each side of the condition must refer to the fields of only one of the views.

      To edit an existing mapping, click on it.

3. Metadata: tab that allows you to define the folder where the new association will be stored and provide a description for the association.

Creating a new association (“Model” tab)¶

Creating a new association (“Properties” tab)¶