The auto-suggest relationships feature allows users to quickly add relationships to projects where physical constraints may not have been defined for some or all of the tables.

Suggested Relationships creates virtual objects which live as metadata inside the project. Virtual Primary Keys (VPK) and Virtual Foreign Keys (VPK) (as well as corresponding virtual relationships) are generated as a result. These virtual constraints allow users to visualize relationships (through existing virtual relationship functionality) without creating physical constraints and forward engineer changes to the database.

Suggested Relationships is a three-step process that can find and suggest (virtual) primary and foreign keys and create these relationships from a guided wizard. The steps include:

Here is a simple example of Suggested Relationships in action:

The Suggested Relationship wizard can be found in the main SqlDBM menu and enabled as part of Reverse Engineering.

Open the main SqlDBM menu and select "Suggested Relationships" to open the wizard.

While performing Reverse Engineering, select the "Suggest relationships" checkbox to open the wizard after pressing "Import" and completing the RE operation.

The Suggested Relationship wizard guides the user through a three-step process. From defining primary key naming parameters, suggesting VPKs, and finally, suggesting VFKs.

Once PK naming patterns have been set, and some PKs (virtual or physical) exist, the wizard can be run on-demand in any order.

Naming Patterns determine which columns will be considered as candidates for VPKs. SqlDBM will search for columns containing the keywords in the "Include" tab while ignoring the columns containing keywords from the "Exclude" tab.

Note that common keywords are provided by default.

Keywords will be saved in the project for subsequent use.

Generate

Once "Patterns" have been set, click the "Find Virtual PK" button or the triangle icon to generate suggestions.

Review

Once suggestions are generated, users are free to review and modify them.

Suggestions for individual tables can be turned on or off individually or in bulk using the corresponding checkboxes next to the table name on the left.

VPK column suggestions can also be modified for each table by clicking on the pencil icon to the right of the corresponding column proposal.

Regenerate

If changes are made to the underlying objects, press the triangle icon to regenerate the suggestions.

Create

Once the VPK suggestions have been reviewed and configured according to business rules, press the "Create [n] VPK" button to finish generating the virtual PKs.

The resulting VPKs will be displayed on the diagram on corresponding columns, similar to physical constraints.

Note, unlike physical PKs, VPK columns will not shift to the top of the table as the intention is not to alter the physical structure.

Virtual Foreign Keys and corresponding Virtual Relationships can be suggested once project tables contain some PKs (physical or virtual). This feature will look for occurrences of PK or VPK columns in other tables and suggest the creation of VFK relationships.

Set Options

The following options exist to fine-tune the VFK suggestions. Remember that the operation can be run many times with different options selected. Select "Set Options" in the "Suggested Relationships" tab to bring up the options.

Generate

On the "Suggested Relationships" tab, click the "Find Virtual Relationship" button or the triangle icon to propose relationships.

Review

Review the suggested relationships and select or unselect proposals using the corresponding checkboxes to the left of the tables.

For identifying relationships, parent and child table order can be swapped using the divergent arrows icon on each suggestion.

Create

Once the VFK suggestions have been reviewed and configured according to business rules, press the "Create [n] Relationships" button to finish generating the virtual FKs.

Suggested Relationships operates on the following logical settings:

See also:

Relationships

Reverse Engineering

Primary Keys

Foreign Keys