IntelliJ IDEA
 
Get IntelliJ IDEA
You are viewing the documentation for an earlier version of IntelliJ IDEA.

Foreign keysUltimate

Last modified: 29 June 2021

Foreign key relationships specify how tables relate to each other and indicate relationships between tables. IntelliJ IDEA recognizes foreign key relationships in your database schema and uses them to construct JOIN clauses. You can see these relationships in the auto-completion list, data navigation, and diagrams.

In IntelliJ IDEA, you can use explicit foreign keys or create virtual foreign keys.

A foreign key is a field or a collection of fields in one table that refers to the primary key in another table. When you create or modify a table, you can clearly define those keys:

The table that contains a foreign key is a child table. The table that contains a candidate key is a referenced or target table. If your database contains explicit foreign key relationships, IntelliJ IDEA automatically uses them in auto-completion, data navigation, and diagrams.

In the following example, activity.activity_id is a primary key, while visitor.activity_id is a foreign key.

Explicit foreign keys

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