In a well-run textbook, foreign keys make every relationship explicit. In a real enterprise, foreign keys are frequently absent. They were dropped for load performance, never added during a rushed migration, or deliberately omitted because the application enforced integrity in code. The relationships still exist. Orders still belong to customers, line items still belong to orders, and payments still reference invoices. The database simply does not tell you so. To understand such a system you have to discover the relationships from the data itself.
The good news is that a genuine relationship leaves fingerprints. When two tables are related, the values in the referencing column are drawn from the values in the referenced key, and they follow a consistent multiplicity. Those two properties, value containment and cardinality, are the backbone of relationship discovery. Everything else is a hint that helps you find candidates faster.
Value containment is the primary signal
The strongest evidence that column B in one table references key A in another is inclusion: nearly every non-null value of B appears as a value of A. If B’s values are a subset of A’s values, the relationship is plausible in the direction from B to A. If they are not, the relationship either does not exist or does not run in that direction. Containment is directional, and getting the direction right matters, because a child referencing a parent looks very different from two unrelated columns that happen to share a type.
Containment is rarely perfect in a legacy system, and that imperfection is informative rather than disqualifying. A relationship where ninety-nine percent of child values match a parent key, with a small set of exceptions, is almost certainly a real relationship plus a data-quality problem. The exceptions are orphans, and they are one of the most useful things discovery can surface.
Cardinality separates the kinds of relationship
Once containment suggests a link, cardinality tells you what kind of link it is. Check whether the referenced values are unique on the parent side and how many times each is used on the child side. A parent key that is unique, referenced many times by the child, is the classic one-to-many. A pairing that is unique on both sides is one-to-one and may indicate a split table or an extension. A join table whose two columns each reference a different parent, with neither unique on its own, encodes a many-to-many relationship.
- One-to-many: unique parent key, repeated on the child side, with child values contained in the parent.
- One-to-one: unique on both sides, often a table split for storage or access-control reasons.
- Many-to-many: a bridge table whose columns each contain into a different parent key.
- Self-reference: a column contained in the same table’s key, encoding hierarchies like manager or parent-account.
Why naming and type are only weak evidence
It is tempting to match customer_id in one table to customer_id in another and call it a relationship. Naming and type similarity are useful for narrowing the search space, because they suggest which pairs are worth testing first. But they are weak evidence, and treating them as proof is how false relationships get baked into a model. Two tables can both have a column called status_id of the same integer type and reference entirely different lookup tables. A column named id can be a surrogate key in one place and a meaningless sequence in another. Worse, the real relationship may connect columns with different names, like account_ref pointing at acct_no, which pure name matching will never find.
Names describe intent and types describe storage. Only the values describe the relationship. Confirm every candidate against the data before you trust it.
The safe pattern is to use names and types to generate candidates, then rank and confirm those candidates with containment and cardinality measured on the actual values. A candidate that survives the data test is a relationship. A candidate that only ever had a matching name is a coincidence.
Orphans are findings, not noise
When you measure containment on real data, you will find child rows whose reference has no matching parent. These orphans are not a reason to discard the relationship. They are often the most valuable output of the whole exercise, because they represent real integrity gaps that the missing foreign key allowed to accumulate: line items pointing at deleted orders, transactions referencing merged accounts, records that survived a partial migration. Quantifying orphans turns a vague worry about data quality into a specific, countable list that owners can act on.
It helps to distinguish structural orphans from expected nulls. A nullable reference that is legitimately empty for some rows is not an orphan. An orphan is a non-null reference whose target does not exist. Keeping that distinction sharp prevents you from reporting normal optionality as a defect.
Turning candidates into a trustworthy model
Relationship discovery is a ranking problem, not a single query. For every promising pair, you compute the containment ratio, the cardinality shape, and the orphan count, then you rank candidates by the strength of that combined evidence. High containment with a clean one-to-many shape and few orphans is a confident relationship. Partial containment with an ambiguous shape is a candidate that needs a human decision or more sampling. Recording the evidence behind each accepted relationship is what makes the resulting model defensible later, when someone asks why the system believes two tables are connected.
In controlled, synthetic validation where declared foreign keys were stripped from a schema and then rediscovered from data alone, containment and cardinality recovered the intended relationships and additionally exposed injected orphans that the original constraints would have prevented. That dual result is the point. Discovering relationships without foreign keys does not just rebuild the map. It also shows you every place where reality has drifted away from what the map assumes.