Your Database Has More Foreign Keys Than You Think

We ran Squish against production databases during beta testing. The pattern was consistent: for every explicit foreign key, there were three to five implicit relationships in the schema. Real, load-bearing relationships that application code depended on daily.

One fintech company had 12 foreign keys defined in PostgreSQL. Discovery found 47 additional implicit relationships with high confidence. Their senior DBA reviewed the results and confirmed 44.

Where Implicit Relationships Come From

The obvious answer is "developers forgot to add the constraint." That is sometimes true, but the real story is more nuanced.

ORMs That Skip Constraints

Rails, Django, SQLAlchemy, and most other ORMs define relationships in application code. ActiveRecord has belongs_to and has_many. Django has ForeignKey fields. These create the application-level behavior of a foreign key without necessarily creating the database-level constraint.

Some ORMs add the constraint by default. Others do not. And when a team switches ORMs or frameworks mid-project, the new tool rarely goes back to audit what the old one left behind.

Performance Concerns

Foreign key constraints carry a cost. Every INSERT, UPDATE, and DELETE on a child table requires a lookup against the parent. On high-throughput tables, teams sometimes drop constraints intentionally to squeeze out write performance.

The relationship still exists. The data still references a parent table. The application code still enforces it. But the database has no record of it.

Cross-Schema and Cross-Database References

You cannot create a foreign key that spans databases. A user_id column in your analytics warehouse that references the users table in your operational database is a real relationship with zero chance of being formally constrained.

This is increasingly common as companies adopt multi-database architectures. The operational PostgreSQL database feeds into Snowflake through an ETL pipeline, and all those carefully defined foreign keys evaporate in transit.

Migrations Gone Wrong

Database migrations are stressful. Teams focus on getting data moved safely and applications running again. Rebuilding every constraint is often on the "we will do it later" list. Later never comes.

Why This Matters

The cynical take is that it does not matter. The application works. Data flows. Reports generate. Why care about undocumented relationships?

Three reasons.

Joining Without Confidence

When an analyst writes a JOIN, they are asserting a relationship between tables. If that relationship is not documented, they are working from tribal knowledge or guesswork. Sometimes they get it wrong, and the result is a subtle data quality issue that nobody catches for months.

dbt and Semantic Layer Models

If you use dbt, you define relationships in YAML. If you use any semantic layer tool, you define relationships in its configuration format. These definitions are only as good as your understanding of the actual schema. Miss an implicit relationship and your semantic model has a blind spot.

Schema Changes Break Things Silently

When a relationship is defined as a foreign key, dropping the parent column produces an error. When it is implicit, dropping the parent column succeeds silently. The child table now contains orphaned references. No error, no warning, just gradually corrupting data.

Finding Them

Manual approaches work but do not scale. You can grep your application code for JOIN statements and cross-reference with your constraint definitions. You can interview developers. You can read ORM model files.

Or you can point Squish at your database and get a complete relationship map in under a minute. We analyze column names, data types, and cardinality patterns to find relationships that match the statistical signature of a foreign key without being defined as one.

Each discovered relationship comes with a confidence score. A column named user_id with matching data types and compatible cardinality against a users.id primary key scores high. A more ambiguous match like status_code referencing a statuses table scores lower. You decide which ones to formalize.

What to Do With Them

Discovering implicit relationships is step one. Here is what to do next.

If you can add the constraint, add it. The write performance cost is real but often overstated. Modern PostgreSQL handles foreign key checks efficiently, and the data integrity guarantee is worth it for most workloads.

If you cannot add the constraint (cross-database, performance-critical, or legacy reasons), document it. Put it in your data catalog. Add it to your dbt YAML. Make it visible so that the next analyst who joins your team does not have to reverse-engineer it.

Either way, stop pretending these relationships do not exist. Your application code already knows about them. Your database should too.