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 Reference" button on the top menu of any diagram. Relationship types will vary based on notation style (IDEF1X or Crow's Foot).

Figure 1. Selecting relationship type for IDEF1X notation

Figure 2. Selecting relationship type for Crow's Foot notation

Click on the parent table and drag the bottom-right knob to the child table.

By default, the foreign keys will be added to as primary keys in the child table. However, this can be changed by dragging the foreign key fields to the bottom part of the child table to make them non-primary.

Figure 3. adding a physical foreign key relationship

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 RELY by clicking on any constraint from the Keys section of the table properties and selecting the RELY option.

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.

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).