Foreign keys

A foreign key is used to define (and visualize) a relationship between two tables.

When the primary key of one table (parent) is added to another table (child), this becomes the child table's foreign key.

SqlDBM supports physical (FK) as well as virtual foreign keys (VFK). VFKs look and feel like physical FKs, but live as metadata objects within the project and are not Reverse or Forward Engineerable.

Virtual foreign key relationships have two main use cases:

To provide FK and relationship visibility on diagrams for databases that do not support physical FKs (i.e., Azure Synapse).
To avoid deployments and physical changes in existing databases while benefiting from relational visibility in a project.

Adding foreign keys

Before adding a Foreign Key, select the relationship type from the "Add relationship" button on the top menu of any diagram. Relationship types will vary based on notation style (IDEF1X or Crow's Foot).

Selecting relationship type for IDEF1X notation

Foreign keys will be added as primary keys to the child table if "Identifying" is selected. In case if "Non-identifying" relationship is selected, foreign keys will be added to the child table as regular attributes.

Selecting relationship type for Crow's Foot notation

By default, the foreign keys created in the Crow's Foot notation will be added as regular fields in the child table.

Adding a physical foreign key relationship

Click on the parent table and drag the bottom-right knob to the child table. Or click on the child table and drag the upper-left knob to the parent table.

Relationship type in this case depends on the type selected in the "Add relationship" button on the top menu:

"Non-identifying" is selected - foreign keys will be added as regular attributes in the child table;
"Identifying" is selected - foreign keys will be added as primary keys in the child table.

A popover will appear to help users quickly set their intended linking behavior by facilitating the following actions:

Use the parent PK column names: by default, the tool will suggest creating a child column with the same name as the parent PK. If a column with that name already exists in the child, it will be used as the reference in the PK.
Rename and create new columns: to create new columns with a different rolename from the parent (e.g., rename ID to EMPLOYEE_ID), type in the new name for the column you wish to change.
Select an existing column reference: to reference an existing column, delete the proposal and select an option from the dropdown list. Only compatible columns (i.e., columns of the same data type) will be displayed.

Press Enter or click out to accept the proposal.

Existing relationship types can be changed by:

by dragging the foreign key fields to the top part of the child table to make them primary keys or to the bottom part of the child table to make them regular fields in both Crow's Foot and IDEF1X notations;
by setting the "Identifying" checkbox in the Foreign Key properties in IDEF1X notation:

by changing the relationship type via the relationship line three-dot menu in IDEF1X notation:

RELY property (Snowflake)

Snowflake constraints (PK, FK, AK) now support RELY in Reverse/Forward Engineering and object properties. The RELY property is a keyword included in the constraint definition and acts as a hint for the Snowflake optimizer to rely on the integrity of the constraint and perform join elimination on views and redundant queries.

Set or unset the RELY option by clicking any constraint in the "Keys" section of the table properties and choosing the 'Rely' option in the 'Options' section.

For FKs, clicking on the relationship line will bring up this menu directly.

An article describing how RELY can improve query cost and performance is available here.

Deleting a relationship

There are several ways to delete a relationship. They include

A. Delete the FK using the context menu on the left-hand explorer menu.

B. Delete the FK from the diagram by right-clicking or selecting the context menu.

C. Delete the FK from the (child) object properties panel.

Note that the child column will remain in the child table after the FK has been deleted.

Also note that changing a parent PK column to non-PK will remove the FK relationship but will not delete the FK. The FK will remain with an empty column selection.

Considerations

FKs can be created without the existence of PKs in the parent tables. These are viewable on the diagram but will not be Forward Engineered since the required parameters are missing.
Virtual FKs can be created in bulk using the Suggested Relationships wizard.
Physical IDEF1X relationships are tied to the structure of the child table, while Crow's Foot are conceptual (i.e., cardinality changes do not impact PK or column order).