IntelliJ IDEA
 
Get IntelliJ IDEA

Virtual foreign keys

Last modified: 11 October 2024

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. To do that, use virtual foreign keys. Virtual foreign key is an IDE virtual object. Alternatively to foreign keys, virtual ones are not defined in the database code.

Consider the following example query:

Let us 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

The virtual foreign key relation is stored in external-data-<data_source_name>.xml. You can select another name for the XML file and other place to store this file. To change or see the path to the XML document, open data source settings by pressing CtrlAltShift0S, click the Options tab and see the Virtual objects and attributes field.

Virtual foreign keys ( Virtual foreign key) can be found in the Database tool window.

Virtual foreign keys in Database