When building a new database from preexisting tables or flat files, is it safe to assume that referential integrity (foreign key consistency) has already been enforced on the received data?

Introduction / Context:
In data migrations and greenfield builds that reuse legacy tables, referential integrity (RI) problems are common. This question targets proper due diligence when inheriting data versus trusting upstream processes blindly.

Given Data / Assumptions:

• Source systems may lack declared foreign keys.
• Exports may have been filtered or transformed, introducing orphaned references.
• Data may span multiple systems with inconsistent codes.

Concept / Approach:
Never assume RI. Instead, profile and validate. Use foreign key discovery, distinct counts, anti-joins to find orphans, and domain checks. Only after cleaning and confirmation should you enforce RI constraints in the target schema.

Step-by-Step Solution:
Inventory candidate parent/child tables and key columns.Run anti-joins to detect orphans: child.key not found in parent.key.Quantify anomalies and determine remediation (fix values, add parents, or drop bad rows).Create and validate foreign keys; add indices to support them.Automate ongoing quality checks in ETL/ELT.

Verification / Alternative check:
Cross-tab counts and integrity reports before and after remediation demonstrate improved consistency. Sample-based manual inspection can confirm edge cases.

Why Other Options Are Wrong:
“Assume RI if production” is risky; production systems can still have errors. NULL presence or surrogate keys do not guarantee RI.

Common Pitfalls:
Trusting documentation without data profiling; missing code table mismatches; overlooking soft-deleted or inactive parent rows.

Final Answer:
Does not apply — never assume RI; profile and validate the data