Loading a child table before the parent table in an ADF pipeline. Deleting rows from a parent table that have referencing child rows. Source data references a foreign key value that was not loaded or was deleted

Step 1: Identify the constraint from the error message. Find the parent and child tables: SELECT fk.name, OBJECT_NAME(fk.parent_object_id) as child, OBJECT_NAME(fk.referenced_object_id) as parent FROM sys.foreign_keys fk WHERE fk.name = 'FK_name';. Step 2: Load parent tables before child tables in the pipeline. In ADF, use dependency between activities to enforce load order.. Step 3: For bulk loads where FK validation can be deferred, temporarily disable the constraint: ALTER TABLE [child] NOCHECK CONSTRAINT [FK_name]; — then re-enable and validate: ALTER TABLE [child] WITH CHECK CHECK CONSTRAINT [FK_name];

Medium severitydata integrity

Power BI Refresh Error:
547

What does this error mean?

An INSERT, UPDATE, or DELETE was blocked by a FOREIGN KEY constraint — the referenced key does not exist in the parent table (or a child row exists in a dependent table).

Common causes

1Loading a child table before the parent table in an ADF pipeline
2Deleting rows from a parent table that have referencing child rows
3Source data references a foreign key value that was not loaded or was deleted

How to fix it

1Step 1: Identify the constraint from the error message. Find the parent and child tables: SELECT fk.name, OBJECT_NAME(fk.parent_object_id) as child, OBJECT_NAME(fk.referenced_object_id) as parent FROM sys.foreign_keys fk WHERE fk.name = 'FK_name';
2Step 2: Load parent tables before child tables in the pipeline. In ADF, use dependency between activities to enforce load order.
3Step 3: For bulk loads where FK validation can be deferred, temporarily disable the constraint: ALTER TABLE [child] NOCHECK CONSTRAINT [FK_name]; — then re-enable and validate: ALTER TABLE [child] WITH CHECK CHECK CONSTRAINT [FK_name];

Frequently asked questions

How do I find all FK constraints on a table?

Run: SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'dbo'; — or: SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('dbo.MyTable');

Is it safe to disable FK constraints during a data load?

It's acceptable for controlled ETL loads if you re-enable with CHECK afterward. The CHECK keyword validates all existing rows against the constraint, ensuring integrity is restored.

Can dbt cause FK violations?

dbt can if models are run in the wrong order or if seeds are loaded before the referenced lookup tables. Use dbt's ref() dependencies to ensure correct build order.

Official documentation: https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-547-database-engine-error