Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

Foreign Key Mode (Model Object)

Applies to
Platform: Microsoft SQL Server · Snowflake · Snowflake dbt · Databricks · Databricks dbt · Fabric Lakehouse · Fabric Warehouse · Oracle
Architecture: Data Vault · Dimensional · Data Mart

Foreign Key Mode (ForeignKeyConstraintMode) decides whether a table gets foreign-key constraints and whether the database checks them. The constraints point to the objects the model object references.

Where to set it

Set the property when you edit a model object. It is offered on these types:

  • Data Vault: all Link and Satellite types, PIT and Business PIT. Oracle does not offer it on Business Driving Key Link.
  • Dimensional: Entity and Fact.
  • Data Mart: Mart Fact. Databricks dbt also offers it on Mart Dimension, Mart Composite, Mart Hierarchy Flatten and Mart Out.

Values and defaults

  • None: no constraints.
  • Create Disabled (default): the constraints are created, but the database does not check them.
  • Create Enforced (Microsoft SQL Server and Oracle only): the constraints are created and checked at the end of each layer load.

Fabric Lakehouse offers only None, which is also its default.

Effect on generated output

  • Microsoft SQL Server: Create Disabled adds the constraint and then disables it with NOCHECK CONSTRAINT.
  • Oracle: both modes create the constraint with RELY DISABLE NOVALIDATE. With Create Enforced, the load enables it at the end of each layer load.
  • Snowflake, Databricks and Fabric Warehouse: the constraint is declared NOT ENFORCED. Databricks declares it only with Use Unity Catalog true.
  • Databricks dbt: the constraint is declared with type: foreign_key in the model YAML files.

With Create Enforced, the constraints are checked at the end of each layer load. When a row has no parent, the load fails at the end of the layer.

Example

A Link on Microsoft SQL Server with Create Enforced references a Hub. Load 1 loads customers 1 and 2 into both. Load 2 delivers customer 3 to the Link only. The load fails at the end of the Raw Vault layer with:

Msg 547: The ALTER TABLE statement conflicted with the FOREIGN KEY
constraint "FK_RDV_LNK_single_customer_hub_lnk_RDV_HUB_load_customer_hub_
CUSTOMER".

Failure symptoms and recovery

The load fails at the end of a layer with error 547

Message: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "<constraint name>"
Cause: a table with Create Enforced holds rows whose parent row is missing, on Microsoft SQL Server.
Fix: load the missing parent rows, for example into the Hub, and run the load again.

Related articles