Virtual Foreign Keys
A virtual foreign key is a relationship between two tables that you define in the plugin configuration, independent of the actual database schema. The AI uses these relationships to understand how tables can be joined — even when no foreign key constraint exists in the database.
When to Use Virtual Foreign Keys
Use virtual foreign keys when:
- Tables are related by a shared column but no
FOREIGN KEYconstraint is defined in the database. - Tables are in different schemas and cannot have native FK constraints.
- The database was designed without referential integrity constraints for performance reasons.
- You want the AI to reliably discover a JOIN path that it would otherwise miss or guess incorrectly.
Without virtual foreign keys, the AI infers relationships from column names and descriptions alone. Explicit FK definitions produce more reliable and consistent JOIN generation.
Defining a Virtual Foreign Key
Open the schema configuration, expand a table, then expand the column you want to link. In the Foreign Key section, set:
| Field | Description |
|---|---|
| Referenced Table | The name of the table this column points to. |
| Referenced Column | The column in the referenced table that this value matches. |
Example: The column customer_id in the orders table points to the id column in the customers table.
Example FK map for an e-commerce schema
| Table | Column | Referenced Table | Referenced Column |
|---|---|---|---|
orders | customer_id | customers | id |
order_lines | order_id | orders | id |
order_lines | product_id | products | id |
invoices | order_id | orders | id |
How Virtual Foreign Keys Affect Query Generation
The AI uses the FK map to build JOIN conditions automatically when a question spans multiple tables.
Without the FK defined, a question like "Show me all orders for customer Arjuna" requires the AI to guess the join condition. With the FK defined, the AI consistently produces:
SELECT o.*
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.name = 'Arjuna'
The more complete your FK map, the fewer JOIN errors you will see in the query testing panel.
Limitations
- Virtual foreign keys are hints to the AI — they are not enforced at query execution time.
- Only define FKs between tables that are both included in the schema configuration.
- Circular FK references (A → B → A) may produce ambiguous JOIN paths.
Related Pages
- Configure Tables and Columns — select which columns to expose before defining FKs.
- Testing Queries — verify that JOIN generation works correctly after adding FKs.
- SQL Database Reader Overview