Table relationships
How to Find Table Relationships When Foreign Keys Are Missing
Use Investigate Relationships to identify, validate, and confirm likely table connections when foreign-key metadata is missing or incomplete.
How can you find table relationships without foreign keys?
Open Investigate Relationships from Schema Discovery or Relationships Overview. SQL Mocker generates a database-specific investigation script for you to run, separates database-defined relationships from suggested connections, and explains the structural evidence for each candidate. You can then run optional aggregate data checks before confirming or rejecting a suggested relationship.
Why a valid relationship may not have a foreign key
Missing constraints do not mean the tables are unrelated. Older applications may enforce relationships in application code, warehouses may avoid constraints during high-volume loading, and reporting databases may combine data from systems that cannot share database-level keys.
These are logical relationships rather than relationships enforced by the database. Investigate them using available metadata and trusted usage while keeping that distinction clear.
Investigate missing relationships in SQL Mocker
1. Open Investigate Relationships
If you are still identifying the relevant tables, open the tool from Schema Discovery. Enter at least two table names after confirming the database system.
If the schema is already loaded, open Relationships Overview and select Investigate Relationships. The tables in the relationship map are preloaded for the investigation.
2. Generate and run the Investigation SQL
Select Generate Investigation SQL. SQL Mocker builds a script for the selected database system and investigation scope. Review the script, run it in your own database tool, then paste the returned result sets or upload a JSON, CSV, TSV, TXT, or Excel export.
The result describes relevant columns, data types, primary and unique keys, and declared references. This initial investigation analyses structural evidence. Aggregate data evidence is added separately only when you select candidates for further data validation.
3. Analyse the investigation results
Select Analyse Results. Relationships explicitly declared in the imported metadata appear under Database-defined Relationships. Possible connections that are not declared by the database appear separately under Candidate Relationships.
Each candidate shows its source and proposed parent table and column, followed by structural evidence such as compatible data types, primary or unique parent keys, matching identifier names, and compatible lengths or precision. Strong structural candidates are labelled Strong candidate — further validation recommended.
4. Run further data validation when needed
Select a candidate for data validation. Where batch validation is available, you can select multiple candidates. Then choose Run Further Data Validation Checks. The generated aggregate checks cover child and parent row counts, populated and null child keys, unmatched references, distinct child keys, and duplicate parent keys.
Run the validation SQL in your database and paste or upload the aggregate result. SQL Mocker displays the outcome under Updated candidate relationships without changing the original structural candidate list.
5. Confirm or reject each suggestion
Confirm a suggested relationship only when the evidence and your understanding of the schema agree. You can also reject the candidate or leave it unconfirmed while you investigate further.
Confirmed suggestions are stored as user-confirmed discovered relationships in the SQL Mocker workspace. They remain distinct from relationships defined by database constraints and do not alter the underlying database.
How structural candidates are identified
Investigate Relationships compares the metadata returned for the selected tables. It looks for a plausible child identifier and a compatible primary or unique key on the proposed parent table. Naming, data type, length, precision, and column descriptions can add supporting evidence.
A candidate is not presented as a database-defined relationship unless the imported metadata contains an explicit reference. Generic names such as id, name, or code still require careful review in the context of the owning tables and their business meaning.
- Compatible types support a candidate but do not establish its business meaning.
- A primary or unique parent key is stronger evidence than a repeated non-key column.
- Matching names and descriptions can strengthen the candidate.
- Database-defined and suggested relationships remain visibly distinct.
What each stage tells you
Investigation stage
Imported relationship metadata
Evidence returned
Connections explicitly defined by the database
Investigation stage
Structural candidate analysis
Evidence returned
Compatible columns, keys, names, lengths, and precision
Investigation stage
Further data validation
Evidence returned
Aggregate row counts, unmatched references, nulls, and duplicate parent keys
Investigation stage
Your review
Evidence returned
Whether the connection matches the schema's intended business meaning
Investigation stage
Confirmation
Evidence returned
A reviewed relationship added to the SQL Mocker workspace
Structural evidence can make a candidate strong before data validation. Aggregate data validation can add support or expose issues, but the final confirmation remains a user decision because metadata and counts cannot establish business meaning on their own.
Interpret the further data validation
You do not need to assemble separate validation queries manually. Investigate Relationships generates database-specific aggregate SQL for the candidates you select. After you paste the result back, the updated candidate shows the structural evidence together with the additional data evidence.
A supported result can report that every populated child reference matched the proposed parent key and that no duplicate parent keys were found. Empty tables produce an inconclusive outcome, while unmatched references or duplicate parent keys produce an issue state that requires closer review.
- Child and parent row counts show how much data was checked.
- Populated and null child-key counts show whether the proposed reference is used.
- Unmatched child references test whether populated values reach the proposed parent key.
- Duplicate parent keys test whether the proposed parent column is unique in the checked data.
- Distinct child keys help interpret the observed relationship pattern.
Understand the likely relationship pattern
Pattern observed in the data
Parent identifier unique, child identifier repeated
Possible interpretation
One-to-many candidate
Pattern observed in the data
Identifier unique on both sides
Possible interpretation
One-to-one candidate
Pattern observed in the data
Identifier repeated on both sides
Possible interpretation
Possible many-to-many relationship or incomplete key
Pattern observed in the data
Significant unmatched values
Possible interpretation
Relationship may require further investigation
These are indicators, not proof. A customers table may contain one row per customer while orders contains many rows per customer, which is consistent with a one-to-many relationship. If both sides repeat customer_id, investigate whether another field or bridge table is required.
Review evidence from existing use
Search trusted views, stored procedures, reports, ETL jobs, existing approved SQL, and database documentation for established joins between the candidate tables. Repeated use of the same join is useful evidence, but it does not make that join correct for every reporting requirement. Consider the grain and purpose of the new query.
Record only relationships you have reviewed
After reviewing structural evidence, optional aggregate validation, trusted SQL or documentation, and relevant business context, confirm only the connection supported by that evidence.
Select Confirm suggested relationship, then continue to Relationships Overview. The connection is labelled as reviewed in Schema Discovery and confirmed by the user, not as a database-defined relationship. SQL Mocker can then use it as context for multi-table SQL generation. See Discover Missing Table Relationships for a walkthrough of this workflow.
The complete workflow
- Known tables: Relationships Overview → Investigate Relationships → Run Investigation SQL → Analyse results → Optionally run further data validation → Confirm or reject suggestions → Continue to Relationships Overview
- Unknown tables: Schema Discovery → Identify likely tables and columns → Investigate Relationships → Run Investigation SQL → Analyse database-defined and candidate relationships → Optionally run further data validation → Confirm or reject suggestions → Continue to Relationships Overview
Investigate and define missing relationships with SQL Mocker
Open Investigate Relationships from Schema Discovery or Relationships Overview. Generate database-specific investigation SQL, review database-defined and suggested connections separately, and optionally add aggregate data-validation evidence. Confirm only the suggestions that match your schema knowledge so SQL Mocker can use them as reviewed context for multi-table SQL.