Local Relationships
When database tables do not have relationships between them, you can create primary and foreign key relationships between columns of different tables directly in MapForce (i.e., local relationships). Any database columns can be used as primary or foreign keys. You can also create new relations in addition to those existing in the database. Locally defined relationships are saved together with the mapping.
The following table lists all the possible fields between which you can define local relations. Mixed relationships are possible (e.g., mapping the output of a stored procedure to a database column). The fields taking part in the relationship must have the same or compatible data types.
Primary/unique key | Foreign key |
|---|---|
•Column of a database table or view •Output parameter or return value of a stored procedure (see also Stored Procedures) •Column of a recordset returned by a stored procedure. Applicable if the stored procedure is called as a data source (without parameters) or as a function (with input and output parameters). In order for the recordset to become available for selection, you must execute the stored procedure once, to retrieve the recordset. •Column of a user-defined SELECT statement (see also SQL SELECT Statements as Virtual Tables) | •Column of a database table or view •Input parameter of a stored procedure •Input parameter of a user-defined SELECT statement |
Example
The BookCatalogNoRelation.sqlite database has two tables: Authors and Books (screenshot below). At this stage, no foreign-key relationship exists between the tables.
DB component without relationships
When we insert the database into the mapping, the database component looks as follows:
Local relationship definition
In this example, our goal is to reference the Authors table in the Books table. Follow the instructions below:
1.Right-click inside the component and select Add/Remove/Edit Database Objects from the context menu.
2.Click the Add/Edit Relations button in the Add/Remove/Edit Database Objects dialog.
3.Click Add Relation in the Add/Edit Relations dialog (screenshot below).
4.Click [select object] in the Primary/Unique Key Object column and select Authors. Then select AuthorID in the [select column] drop-down list.
5.Click [select object] in the Foreign Key Object column and select Books. Then select AuthorID in the [select column] drop-down list.
6.Click OK to complete the local relation definition.
DB component with relationships
As soon as you have finished defining the local relations, the database component becomes available in the mapping area (screenshot below). The component displays two possible database structures. In each of these structures, the root table is different. For example, in the expanded structure below, Authors is the root table. Depending on your needs, you can map data to and from any of the structures available in the component. You can also mix and match tables from different structures in the component. For more information about these scenarios, see Database Relationships.
