Discovering Join Paths For Your AI Agent

Renat Iuldashev

· updated
Abstract

Real databases often contain join paths that exist in the data but are absent from declared constraints, and schema-linker finds them. It treats every inferred relationship as a candidate: names propose it, several independent signals support it, and exact value containment verifies it.


The AI agent sometimes chooses the wrong tables or invents a join because real databases have incomplete foreign keys. For example, ‘support_tickets.customer_id’ may refer to ‘customers.customer_id’ without a declared constraint. The challenge is that shared names or values can also create convincing nonsense. I built schema-linker to help an AI agent discover missing joins without mistaking coincidence for structure. It supports SQLite, PostgreSQL, MySQL, MariaDB, and DuckDB.

How it works

schema-linker checks possible relationships from cheapest to most expensive, discarding weak candidates at each step.

1. Find plausible column pairs

It starts with declared primary and foreign keys, column names and types, row counts, and estimated distinct-value counts. Existing foreign keys are already known, incompatible data types cannot form useful joins, and near-unique free-text columns are poor identifiers, so those pairs are skipped. Similar names such as orders.customer_id and customers.customer_id identify the remaining pairs worth checking against the data.

2. Check which column points to which

A foreign-key relationship only needs to work in one direction: every value in orders.customer_id must exist in customers.customer_id, but many customers may have no orders. Suppose the orders contain customer IDs {1, 2} and the customers contain {1, 2, 3, 4, 5}. Jaccard similarity divides the two shared values by the five values found in either column, giving only 40%. schema-linker asks a more useful question: what percentage of the order customer IDs exist in the customer table? Here, both of them do, so the result is 100%. To avoid comparing every pair value by value, MinHash creates a small fingerprint of each column’s values, and LSH Ensemble uses those fingerprints to find column pairs likely to match. Only those pairs receive an exact check: in memory for small sets, or with an SQL anti-join for large ones.

Finding every value from one column in the other is not enough by itself. Two unrelated flags containing 0 and 1 have the same values but would create a many-to-many join. schema-linker rejects such flag pairs unless one column is a primary or unique key, and reports a relationship only when at least three independent signals agree.

3. Group evidence into a compact join map

Pairwise output multiplies noise: four columns in one customer-ID domain can produce many redundant relationships. schema-linker groups them under a primary-key anchor. A report with declared links enabled can look like this:

# Schema Links
- version: 0.0.5
- dialect: sqlite
- database: examples/shop.sqlite
- schema: main
## Declared PK/FK Links
order_lines.order_id -> orders.order_id
orders.customer_id -> customers.customer_id
## Inferred Links
### customers.customer_id
- inferred: support_tickets.customer_id
- declared: orders.customer_id

FAQ

How do I find join paths that are not declared as foreign keys? Use schema-linker. It proposes candidate column pairs from names, types, and cardinalities, filters them with MinHash and LSH Ensemble fingerprints, then verifies exact value containment in memory or with an SQL anti-join.

What are undeclared foreign keys? Relationships that exist in the data but not in the schema constraints: support_tickets.customer_id may refer to customers.customer_id with no declared foreign key.

How does schema-linker avoid false join candidates? It rejects pairs whose values contain each other in both directions unless one side is a primary or unique key, and reports a relationship only when at least three independent signals agree.

Which databases does schema-linker support? SQLite, PostgreSQL, MySQL, MariaDB, and DuckDB.

Why not use Jaccard similarity for join detection? schema-linker measures directional containment instead of symmetric.

How do I try schema-linker? Install it from PyPI or read the source on GitHub; it runs against SQLite, PostgreSQL, MySQL, MariaDB, and DuckDB databases.

References

  1. Automatic Metadata Extraction for Text-to-SQL
  2. schema-linker source code
  3. DuckDB
  4. Jaccard index
  5. MinHash
  6. Locality-sensitive hashing
  7. schema-linker on PyPI