Fixing SQL BACPAC Foreign‑Key Import Failures in Azure SQL

by

Summary

During a BACPAC import of a SQL Server database, the process fails with a foreign‑key constraint error (Msg 547). The source production database shows no orphaned rows, yet the import aborts because the data being loaded does not satisfy the constraint at the moment the ALTER TABLE … CHECK CONSTRAINT statement runs.

Root Cause

  • BACPAC imports load data without enforcing constraints, then issue a WITH CHECK CHECK CONSTRAINT after all rows are inserted.
  • The import process was interrupted by a long‑running export, causing transaction log truncation and row ordering issues on the target.
  • Orphan rows were temporarily inserted because the order of table restores placed child rows (TmX_Loan_Application) before their parent rows (TmX_Order).
  • When the constraint check runs, those orphan rows are still present, triggering the foreign‑key conflict.

Why This Happens in Real Systems

  • BACPAC is schema‑plus‑data; it does not preserve the original transaction order.
  • Azure SQL Database’s DTU spike during export can delay background tasks (e.g., index rebuilds, statistics updates), altering the timing of data insertion on restore.
  • Large tables with many foreign‑key relationships often require a specific restore order; BACPAC’s generic order can violate that order.
  • Implicit batching may split a logical set of rows, leaving a child batch without its parent batch in the same transaction.

Real-World Impact

  • Import failures force manual intervention, delaying deployments or migrations.
  • Data inconsistency risk if the import is forced with NOCHECK, leaving the database in a partially invalid state.
  • Operational downtime while troubleshooting, especially in CI/CD pipelines that rely on automated BACPAC restores.
  • Increased cost due to repeated export/import cycles and the DTU spikes observed.

Example or Code (if necessary and relevant)

-- Example: Re‑enable constraints after a successful import
ALTER TABLE dbo.TmX_Loan_Application WITH CHECK CHECK CONSTRAINT FK_TmX_Loan__Order_22800C64;

How Senior Engineers Fix It

  • Pre‑export validation: Run SELECT ... LEFT JOIN checks and also verify referential integrity with DBCC CHECKCONSTRAINTS.
  • Disable constraints during import, then re‑enable with CHECK after all tables are loaded in the correct order:
    • Script the import to NOCHECK constraints first.
    • Load parent tables before children.
    • Run ALTER TABLE … WITH CHECK CHECK CONSTRAINT ALL after data load.
  • Use a DACPAC + data‑pump (e.g., SqlPackage.exe /Action:Import with /p:ImportExportOrder=...) to control table load order.
  • Split the BACPAC: Export large tables separately, restore parents first, then children.
  • Monitor DTU/IO and throttle the export to avoid resource spikes that could cause timeouts or ordering glitches.
  • Automate post‑import validation: Re‑run orphan checks and DBCC CHECKCONSTRAINTS in the target environment.

Why Juniors Miss It

  • They assume foreign‑key constraints are always enforced during data load, not realizing BACPAC’s two‑step process.
  • They often overlook the importance of table load order and treat the import as a black box.
  • Lack of experience with Azure SQL resource throttling leads them to ignore DTU spikes and their side effects.
  • They may skip post‑import integrity checks, believing the source query is sufficient, and thus miss transient orphan rows introduced by the import sequence.

Leave a Comment