Virtual foreign keys

Last modified: 20 December 2022

There are cases when you do not want to use explicitly-defined foreign keys. Reasons for not using foreign keys might include performance issues (in CRUD operations), database characteristics (databases like ClickHouse and Apache Cassandra do not support foreign keys), usage of temporary tables (for testing), personal reasons, and other.

In this case, you can still create foreign key relations without changing your database code. Alternatively to foreign keys, virtual foreign keys are not defined in the database code.

Consider the following example query:

Let's assume that visitor_id is not defined as a foreign key in the database. You can still use this virtual relation between the visitor_id field in the activity table and the id field in the visitor table in this JOIN clause. You can save this relation and use it later or configure rules for virtual foreign keys in settings by using regular expressions.

Virtual foreign keys

Virtual foreign keys can be seen in the Database tool window. You can see a reference on node and object icons in the Icons for data sources and their elements chapter of Database tool window topic.

For the table column icons, refer to Possible icon combinations for columns.