Work with relations

dbForge Studio for SQL Server supports the following types of relations between tables in a diagram:

  • Physical relation – Represents a foreign key relationship defined between tables in the database schema.
  • Virtual relation – Specifies a relationship that is displayed in the diagram but that is not defined in the database.

The table describes a comparison of physical and virtual relations in SQL Server diagrams.

Characteristic Physical relation Virtual relation
Purpose Maintain data integrity and enforce valid relationships. Visualize or document logical relationships.
Exists in database Yes No
Creation Created automatically from an existing FOREIGN KEY constraint or manually by creating a FOREIGN KEY constraint. Created manually in the diagram.
Constraint Enforced as a foreign key constraint in the database. Not enforced; exists only in the diagram.
Visibility Displayed automatically in diagrams. Only visible in the diagram where it is created.

Physical and virtual relations in a diagram

Physical relations

Physical relations represent foreign key relationships between tables in the database and appear as connecting lines in the diagram. Their appearance depends on the selected notation.

Create a physical relation

1. On the Database Diagram toolbar, click New Relation.

2. Drag the child table to the parent table.

3. In the New Foreign Key dialog, configure the relationship properties:

3.1. On the General tab, do the following:

  • In Foreign key name, enter a name for the physical relation, for example, FK_Shift_EmployeeDepartmentHistory_BusinessEntityID.
  • Under Table, select the checkbox next to the column that will participate in the relation. The selected column appears in the Constraint columns list.
  • Under Referenced Table, select the checkbox next to the corresponding column. The selected column appears in the Referenced columns list.

3.2. Optional: On the Advanced tab, select the options:

  • Delete rule – Specifies the action to take when a referenced row is deleted. Available options are NO ACTION (default), RESTRICT, CASCADE, and SET NULL.
  • Update rule – Specifies the action to take when a referenced row is updated. Available options are NO ACTION (default), RESTRICT, CASCADE, and SET NULL.
  • Not for Replication – Specifies whether the foreign key constraint is enforced during replication operations on this table.

4. Click Apply Changes.

Create a physical relation

Note

Relations in the diagram reflect foreign key relationships in the database. When you create a relation in the diagram, dbForge Studio creates the corresponding foreign key constraint in the database. If the constraint cannot be created, the relation is not added to the diagram.

Edit a relation

1. In the diagram, double-click a relation you want to edit.

Alternatively, right-click the relation and select Edit Foreign Key.

2. On the Constraint tab of Table Designer that opens, make changes.

3. Click Apply Changes.

Change the parent table in a relationship

To change the parent table in a relationship, drag the parent table end of the relation to another table.

Note

  • The child table can’t be changed.

  • Relationship cardinality is determined automatically based on the foreign key constraint and can’t be set manually. To change cardinality, modify the foreign key constraint on the child table.

Remove a relation from a diagram

1. In the diagram, select the relation you want to delete and press Delete.

2. In the confirmation dialog, click Yes to confirm the deletion.

Warning

Removing a relation from the diagram also removes the associated foreign key constraint from the database.

Reroute a relationship

To reroute a relation, right-click the selected relationship and select Reroute. This option automatically recalculates the relationship line to adjust its path between the tables.

Adjust line bending

To adjust line bending, select a relationship and drag its bend handle.

Use bend handles to adjust line bending

Virtual relations

Virtual relations allow you to define relationships between tables when no foreign key constraint exists in the database. They are displayed as thin dotted lines connecting tables and exist only in the diagram.

Note

Virtual relations can be converted to physical foreign keys.

Create a virtual relation between columns

1. On the Database Diagram toolbar, click New Virtual Relation.

2. Drag the child table to the parent table.

The virtual relation appears on the diagram.

Create a virtual relation between tables

1. On the Database Diagram toolbar, click New Virtual Relation.

2. Drag the child table to the parent table.

3. In the Virtual Relation Properties dialog, configure the relationship properties:

3.1. On the General tab, enter a foreign key name and select the constraints and referenced columns.

3.2. Optional: On the Advanced tab, select the options:

  • Delete rule – Specifies the action to take when a referenced row is deleted. Available options are NO ACTION (default), RESTRICT, CASCADE, and SET NULL.
  • Update rule – Specifies the action to take when a referenced row is updated. Available options are NO ACTION (default), RESTRICT, CASCADE, and SET NULL.
  • Not for Replication – Specifies whether the foreign key constraint is enforced during replication operations on this table.

4. Click OK.

Create a virtual relation

Edit a virtual relation

1. Double-click the virtual relation.

2. In the Virtual Relation Properties dialog, make the changes.

3. Click OK.

Work with virtual relations in Virtual Relation Manager

You can view, edit, and remove virtual relations, generate their DDL, or convert them to foreign keys in Virtual Relation Manager.

Open Virtual Relation Manager

You can open Virtual Relation Manager in one of these ways:

  • On the Database Diagram toolbar, click Virtual Relation Manager.

  • On the menu bar, select View > Other Windows > Virtual Relation Manager.

The table describes options available in Virtual Relation Manager.

Name Description
Name Indicates the name of the virtual relationship.
Table Indicates the child table that contains the foreign key constraint.
Columns Indicates the columns in the child table that reference the parent table.
Referenced Table Indicates the parent table being referenced by the foreign key.
Referenced Columns Indicates the columns in the parent table that are being referenced.
Delete Rule Specifies the action to take when a referenced row is deleted. Available options are NO ACTION (default), RESTRICT, CASCADE, and SET NULL.
Update Rule Specifies the action to take when referenced key values are updated. Available options are NO ACTION (default), RESTRICT, CASCADE, and SET NULL.

Virtual Relation Manager

Manage virtual relations

In the Virtual Relation Manager grid, right-click a relationship and select one of the following options:

  • Edit Virtual Relation – Opens the Virtual Relation Properties dialog to edit the virtual relation.
  • Remove from Diagram – Removes the selected virtual relation from the diagram.
  • Generate Schema Script – Generates a schema script in a new SQL document.
  • Convert to Foreign Key – Converts the selected virtual relation to a foreign key after you confirm this operation.

Delete a virtual relation

To delete a virtual relation, select it on the diagram or in Virtual Relation Manager and press Delete.

Manage comments

Each relation can display a comment that contains the foreign key name. By default, comments are displayed next to the child table end of relations.

If comments are turned off, on the Database Diagram toolbar, click Show Comments to turn them on.

Remove a comment

To remove a comment, select the relationship for which you want to remove a comment and press Delete. To display it again, right-click the relation and select Show Comment.

Change the comment position

To change the comment position, drag the comment to the desired location. The comment moves with the relationship.

Turn relationships on and off

You can turn a foreign key relationship on or off to control whether it enforces data integrity rules in the database.

To turn a relationship on, right-click the selected relationship and select Enable. This activates the foreign key and ensures data in the child table aligns with the parent table.

To turn a relationship off, right-click the selected relation and select Disable. This deactivates the foreign key, so relationship rules are not enforced, while the relationship remains defined.

Note

Turning a relationship off may allow data that violates the foreign key, potentially leading to errors when the relationship is re-enabled. Use with caution.

Clear waypoints

The Clear Waypoints option removes all manually set waypoints from the line. This can be useful if the relationship line has become too complex or if you want to remove any unnecessary bends or curves in the diagram.

To clear waypoints, right-click the selected relation and select Clear Waypoints.

Adjust relation shapes

To adjust relation shapes, right-click the selected relationship, select Shape, then select one of the following options:

  • Bring to Front – Moves the relationship in front of overlapping objects.
  • Send to Back – Moves the relationship behind overlapping objects.

Find in Database Explorer

To find a relationship in Database Explorer, right-click the selected relationship and select Find in Database Explorer. The foreign key is highlighted in Database Explorer.

Synchronize a relationship with Document Outline

To display the newly created relationship in Document Outline, right-click the selected relationship and select Synchronize Document Outline.