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

Figure 1. 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.

Figure 2. 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.

Figure 3. 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.

Relationship type can be changed:

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.

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