Why Foreign Keys Are Not Enough for Enterprise Join Discovery

# Why Foreign Keys Are Not Enough for Enterprise Join Discovery

Foreign keys are useful. They tell systems how tables are supposed to connect.

But in many enterprise environments, they are only a small part of the relationship story.

Modern data stacks contain operational databases, warehouses, data marts, BI models, dbt projects, ETL pipelines, spreadsheets, third-party systems, and years of analyst-written SQL. The relationships that matter to business reporting and AI-generated SQL often live across all of these layers, not only inside database constraints.

That creates a real problem for AI systems.

An AI agent or NL2SQL workflow may be able to inspect table names, column names, and schema metadata. It may even see a few declared foreign keys. But that does not mean it understands how enterprise data is actually connected.

For AI-generated SQL to be reliable, relationship discovery needs to go beyond foreign keys.

---

## The Foreign Key Assumption

A common assumption is simple:

> If two tables are related, the database should already have a foreign key.

That sounds reasonable in a textbook data model. It is less true in real enterprise systems.

Foreign keys are often missing, incomplete, outdated, or deliberately disabled. Some systems were not designed for analytics. Some were built over many years by different teams. Some were migrated from legacy platforms. Some relationships exist in business processes but were never declared in the database.

Even in well-managed environments, declared foreign keys usually describe only part of the data landscape.

They may tell you how one operational application stores transactions. They usually do not tell you:

- which joins analysts actually use;

- which joins are approved for official metrics;

- which paths create duplicate counts;

- which relationships cross systems;

- which joins are deprecated;

- which join path is correct for a specific business question.

That is why foreign keys are helpful evidence, but they are not a complete relationship map.

---

## Why Foreign Keys Are Often Missing

Enterprise databases frequently lack complete foreign keys for practical reasons.

### Performance and operational constraints

Some production systems avoid enforcing foreign keys because of performance, deployment, or operational concerns. Teams may choose application-level validation instead of database-level constraints.

The data may still be related, but the relationship is not visible as a formal database object.

### Legacy system evolution

Enterprise systems evolve over time. Tables are added, renamed, copied, archived, or merged. Business logic moves from one application to another. Old relationships may remain in use even when the original schema design is no longer clear.

In this environment, the absence of a foreign key does not mean the absence of a relationship.

### Data warehouse modeling choices

Analytical warehouses often transform operational data into facts, dimensions, snapshots, bridge tables, aggregates, and marts. These models may intentionally avoid database-enforced constraints.

The relationship knowledge may be stored in dbt models, BI datasets, semantic layers, documentation, or analyst conventions instead.

### Cross-system relationships

Many important enterprise relationships cross system boundaries.

A customer in a CRM may relate to an account in billing, a contract in ERP, a ticket in support, and a user in a product analytics system. These relationships may be essential for business analysis, but no single database foreign key can represent the full connection.

### Inconsistent naming and identifiers

The same business entity may appear under different names:

- `customer_id`

- `cust_id`

- `account_id`

- `client_code`

- `party_id`

- `external_customer_key`

A declared foreign key may be missing, but repeated usage patterns may show that two fields are connected. The opposite is also possible: similar names may look related but should not be joined.

This is where relationship discovery requires evidence, not simple name matching.

---

## The Risk for AI-Generated SQL

When foreign keys are missing, AI systems tend to guess.

A model may see two columns with similar names and generate a join. In simple cases, this may work. In enterprise data, it can quietly create wrong answers.

The query may run successfully. The result may look clean. The business user may trust the answer. But the join may be wrong.

Common failure modes include:

- joining on a field that looks similar but represents a different entity;

- choosing a direct join when an approved bridge table is required;

- ignoring a required date-effective relationship;

- creating many-to-many fanout and inflating metrics;

- mixing operational and reporting tables incorrectly;

- using a deprecated relationship from an old model;

- applying the wrong relationship path for a specific metric.

These errors are hard to detect because they are not syntax errors. They are context errors.

AI-generated SQL does not only need to know that two tables can be joined. It needs to know whether they should be joined, how they should be joined, and under what business conditions that join is valid.

---

## Relationship Discovery Needs Multiple Evidence Sources

A better approach is to treat relationship discovery as an evidence-based process.

Declared foreign keys should be included, but they should not be the only signal.

### 1. Schema metadata

Schema metadata provides the starting point:

- table names;

- column names;

- data types;

- primary keys;

- unique constraints;

- declared foreign keys;

- nullable fields;

- indexes.

This helps identify obvious candidates, but it rarely provides enough confidence by itself.

### 2. Naming patterns

Column names can provide useful hints. Fields such as `customer_id`, `order_id`, or `region_code` may suggest relationships.

But names are only weak evidence. Many enterprises reuse similar terms inconsistently. A field called `account_id` may refer to a billing account in one table and a sales account in another.

Naming patterns should suggest candidate relationships, not confirm them.

### 3. Query history

Historical SQL can reveal how data teams actually join tables.

If many approved reports join `customers.customer_id` to `orders.customer_id`, that is useful evidence. If a join appears in production dashboards, dbt models, or recurring reports, it may indicate a relationship that matters in practice.

But query history also needs review. A frequently used join is not automatically correct. It may reflect old habits, legacy logic, or duplicated mistakes.

Query history should be treated as evidence, not truth.

### 4. BI and semantic models

BI datasets, metrics layers, and semantic models often contain important relationship logic.

They may show:

- which dimensions are approved for a metric;

- which fields support business filters;

- which joins are used in dashboards;

- which tables are certified or deprecated;

- which aggregations are expected.

This context is especially important when AI-generated SQL is expected to match business reporting.

### 5. ETL, ELT, and dbt logic

Transformation logic can reveal how data is shaped before it reaches analytics users.

dbt models, ETL scripts, stored procedures, and data pipelines often contain join patterns that do not appear as database constraints. They may also define intermediate models that represent approved relationships.

For AI systems, this logic can help distinguish a raw technical relationship from a governed analytical path.

### 6. Optional profiling or statistical signals

In some environments, teams may choose to use profiling or statistics to improve validation. For example, overlap, uniqueness, null rate, or inclusion patterns can help evaluate whether a candidate relationship is plausible.

This should be controlled by the customer and aligned with security policies. Metadata-first discovery should not require uncontrolled movement of raw business data.

Statistical validation can strengthen confidence, but it should not replace business approval.

### 7. Human validation and governance

The final layer is human validation.

Data engineers, analytics engineers, data stewards, and domain experts need a way to approve, reject, annotate, and version relationships. This prevents candidate relationships from becoming trusted context too early.

Relationship discovery should accelerate human review, not remove it.

---

## Candidate Relationships Are Not Trusted Relationships

One of the most important distinctions is between candidate relationships and trusted relationships.

A candidate relationship means:

> These two fields may be related based on available evidence.

A trusted relationship means:

> This relationship has enough evidence, validation, and governance status to be used in AI-generated SQL or analytics workflows.

This distinction matters because AI systems need different behavior depending on confidence.

For example:

- high-confidence relationships may be used directly;

- medium-confidence relationships may require explanation or review;

- low-confidence relationships may trigger clarification;

- rejected relationships should not be reused silently;

- deprecated relationships should be flagged or blocked.

Without this governance layer, relationship discovery can create more risk than value.

---

## Join Paths Matter More Than Individual Joins

Enterprise questions often require more than one join.

A user may ask:

> What was customer lifetime value by region last quarter?

To answer this, the system may need to connect customers, orders, refunds, dates, regions, and possibly account or billing tables.

The problem is not just whether `customers` can join to `orders`. The problem is which full path should be used for this question.

A trusted join path should answer:

- which tables are involved;

- which keys connect them;

- whether the path is direct or indirect;

- whether the path creates fanout risk;

- whether the path is approved for the metric;

- what evidence supports the path;

- whether there are known limitations.

Foreign keys alone rarely provide this level of context.

AI-generated SQL needs relationship intelligence at the path level, not just the column-pair level.

---

## A Practical Example

Imagine an enterprise has these tables:

- `customers`

- `accounts`

- `orders`

- `refunds`

- `contracts`

- `regions`

- `sales_territories`

Several fields look joinable:

- `customers.customer_id`

- `orders.customer_id`

- `refunds.customer_id`

- `accounts.account_id`

- `contracts.account_id`

- `regions.region_id`

- `sales_territories.region_code`

A simple AI system may infer joins from names and generate a query quickly. But a data team may know that:

- revenue should come from `orders`, not `contracts`;

- refunds must be subtracted only when they are settled;

- region should come from the customer’s billing region, not the sales territory;

- enterprise accounts must be joined through `accounts`, not directly through customer records;

- one relationship creates duplicate rows unless a bridge table is used.

None of this is guaranteed to be visible from foreign keys.

The AI system needs business definitions, relationship evidence, join path rules, and policy context before generating SQL.

---

## What Good Relationship Discovery Looks Like

A practical enterprise relationship discovery system should support several capabilities.

### Discover

It should identify candidate relationships across databases, warehouses, BI models, query history, and transformation logic.

### Validate

It should evaluate relationships using multiple evidence sources, including metadata, constraints, query patterns, semantic usage, and optional profiling where allowed.

### Score

It should assign confidence levels so teams and AI systems can understand the strength of each relationship.

### Explain

It should show why a relationship or join path is recommended, including the evidence behind it.

### Govern

It should allow teams to approve, reject, version, deprecate, and audit relationships over time.

### Serve

It should expose trusted relationships and join paths through APIs, MCP, BI integrations, or AI query workflows.

This is the difference between a static schema map and a governed relationship layer.

---

## A Relationship Readiness Checklist

Use this checklist to evaluate whether your enterprise data environment is ready for AI-generated SQL.

| Area | Key question | Ready if... |

|---|---|---|

| Foreign keys | Are declared keys available and reliable? | Existing keys are captured, but not treated as the only relationship source. |

| Metadata | Can candidate relationships be discovered from schema structure? | Tables, columns, keys, types, and constraints are accessible. |

| Query history | Can historical joins be analyzed? | Approved SQL, dashboards, and recurring reports reveal real usage patterns. |

| BI logic | Are certified reporting relationships visible? | Semantic models and BI datasets expose approved joins and dimensions. |

| Transformation logic | Are dbt or ETL relationships available? | Pipeline logic can be used as evidence for analytical relationships. |

| Confidence | Are relationships scored? | Each relationship has evidence, confidence, and validation status. |

| Governance | Can relationships be approved or rejected? | Teams can manage lifecycle, ownership, versions, and exceptions. |

| Join paths | Can complete paths be explained? | AI can retrieve recommended join paths, not just table pairs. |

| Risk handling | Are dangerous joins flagged? | Many-to-many, fanout, deprecated, or low-confidence paths trigger review. |

| Integration | Can AI systems use the context? | Relationship context is available through API, MCP, or query workflows. |

---

## What This Means for Data Teams

Foreign keys should be part of your relationship foundation. But they should not define the boundary of what your AI systems can understand.

For enterprise AI and analytics, the more useful question is not:

> Do we have foreign keys?

The better question is:

> Do we have enough validated relationship context for AI to choose the right join path?

Answering that question requires a broader view of the data stack. It requires metadata, query history, transformation logic, BI models, semantic definitions, policy context, and human governance.

This does not mean teams need to model everything perfectly before using AI. It means teams should start with the domains and questions where wrong joins create the most risk.

A practical starting point is to identify:

1. the most commonly used metrics;

2. the tables most often joined for those metrics;

3. the relationships that are missing or undocumented;

4. the joins that create fanout or duplicate-counting risk;

5. the paths that should be approved, rejected, or reviewed;

6. the context AI needs before generating SQL.

This turns relationship discovery into a focused, value-driven process.

---

## Where Arisyn Fits

Arisyn IntaLink is designed to help data teams discover, validate, score, and govern table relationships and trusted join paths across enterprise data environments.

Instead of relying only on declared foreign keys, IntaLink can support relationship discovery using metadata, constraints, query patterns, BI logic, transformation context, and customer-controlled validation workflows. The goal is not to claim that every relationship can be fully automated. The goal is to help teams find the right candidates faster, evaluate the evidence, and serve trusted join context to AI and analytics systems.

Semora complements this by mapping business terms, metrics, dimensions, and formulas to governed data fields. Together, IntaLink and Semora help AI agents, NL2SQL workflows, and BI tools understand both sides of the problem:

- what the business question means;

- how the data should be connected.

That combination is what makes AI-generated SQL more reliable, explainable, and governed.

---

## Final Thought

Foreign keys are valuable, but they are not enough.

Enterprise data relationships are often hidden in query history, BI models, transformation logic, business rules, and human practice. AI systems need access to that context before they generate SQL.

The goal is not to make AI guess better.

The goal is to stop AI from guessing at all.