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

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

See also:

Primary keys

Suggested Relationships

Did this answer your question?