# Virtual 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. 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:

```SQL
SELECT * FROM activity JOIN visitor ON visitor_id = visitor.id
```

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](#create_a_virtual_foreign_key_in_query_console) and use it later or [configure rules](#configure-rules-for-virtual-foreign-keys) for virtual foreign keys in settings by using regular expressions.

![Virtual foreign keys](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_implicit_foreign_key.svg)

> **Tip:**
> If you have foreign keys pointing to the objects in another schema and these objects have the same names, they will be displayed as qualified in the navigation dialog for 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   `Shift+Enter` (Windows), `⌘ I` (macOS), `⇧ ⏎` (IntelliJ IDEA Classic (macOS)), `⌘ I` (macOS System Shortcuts), `Shift+Enter` (XWin), `Shift+Enter` (GNOME), `Shift+Enter` (KDE), `Shift+Enter` (Emacs), `Shift+Enter` (Sublime Text), `⌘ I` (Sublime Text (macOS)), `Shift+Enter` (NetBeans), `Shift+Enter` (Visual Studio), `⌘ I` (Visual Studio (macOS)), `Shift+Enter` (Eclipse), `⌘ I` (Eclipse (macOS)) , click the Options tab and see the Virtual objects and attributes field.

Virtual foreign keys (          ![Virtual foreign key](https://resources.jetbrains.com.cn/help/img/idea/2026.2/database-plugin.icons.expui.virtualForeignKey.svg)   ) can be found in the   Database   tool window   .

* For the reference on other node and object icons, refer to the [Data sources and their elements](database-tool-window.html#icons-for-data-sources-and-their-elements) chapter of [Database tool window](database-tool-window.html) topic.

* For the table column icons, refer to the [Possible icon combinations for columns](database-tool-window.html#possible-icon-combinations-for-columns) chapter.

* Hide, sort, filter, and group tree objects using the tree objects view options in the [View Options](database-tool-window.html#view_options) menu.

![Virtual foreign keys in Database](https://resources.jetbrains.com.cn/help/img/idea/2026.2/database_object_virtual_foreign_key.png)

## Create a virtual foreign key

In Modify dialog:

Procedure:

1. In the   Database   tool window ,    expand the data source tree until the nodes of    tables .

2. Right-click the        table  node and select `New |          Virtual Foreign Key` .

3. In the Modify  dialog that opens, enter the name of your virtual foreign key in the Name field.

4. In the Target Table pane, specify the name of the target table.

5. In the Columns pane, click the Add button (![the Add button](https://resources.jetbrains.com.cn/help/img/idea/2026.2/app.expui.general.add.svg)).

6. In the Column Name field, specify the name of the column in the child table.

7. In the Target Name field, specify the name of the column in the target table.

8. Click OK to  add your virtual foreign key    .

9. If the Save external data for <data_source_name> dialog opens, specify the directory for `external-data-<data_source_name>.xml` file and click Save.

![Create a virtual foreign key in the Modify dialog](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_create_virtual_foreing_key_in_modify_dialog.png)

In query console:

Procedure:

1. Click the table relation in the `ON` clause and press `Alt+Enter` (Windows), `⌥ ⏎` (macOS), `⌥ ⏎` (IntelliJ IDEA Classic (macOS)), `⌥ ⏎` (macOS System Shortcuts), `Alt+Enter` (XWin), `Alt+Enter` (GNOME), `Alt+Enter` (KDE), `Alt+Enter` (Emacs), `Alt+Enter` (Sublime Text), `⌥ ⏎` (Sublime Text (macOS)), `Alt+Enter` (NetBeans), `Alt+Enter` (Visual Studio), `⌥ ⏎` (Visual Studio (macOS)), `Ctrl+1` (Eclipse), `⌘ 1` (Eclipse (macOS)).

2. Select Store table relation.

3. If the Save external data for <data_source_name> dialog opens, specify the directory for `external-data-<data_source_name>.xml` file and click Save.

![Store table relation](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_store_table_relation.png)

Procedure: Create rules for virtual foreign keys

You can use regular expressions to create a rule according to which IntelliJ IDEA will point a column in one table to a column in another table.

1. Open settings (`Ctrl+Alt+S` (Windows), `⌘ Comma` (macOS), `⌘ Comma` (IntelliJ IDEA Classic (macOS)), `⌘ Comma` (macOS System Shortcuts), `Ctrl+Alt+S` (XWin), `Ctrl+Alt+S` (GNOME), `Ctrl+Alt+S` (KDE), `Ctrl+Alt+S` (Emacs), `Ctrl+Alt+S` (Sublime Text), `⌘ Comma` (Sublime Text (macOS)), `Ctrl+Alt+S` (NetBeans), `Ctrl+Alt+S` (Visual Studio), `⌘ Comma` (Visual Studio (macOS)), `Ctrl+Alt+S` (Eclipse), `⌘ Comma` (Eclipse (macOS))) and navigate to `Database | Other`.

2. Locate the Virtual Foreign Key section.

3. In the table, click the Add button (![click the Add button](https://resources.jetbrains.com.cn/help/img/idea/2026.2/app.expui.general.add.svg)).

4. Double-click the Column pattern cell and type the regular expression that will match a column name that you want to use as a virtual foreign key.

5. Double-click the Target column pattern cell and type the replacement pattern. The replacement pattern uses the match from the Column pattern expression and is interpreted as a regular expression.

You can check your rules by using the Check... button (![the Check button](https://resources.jetbrains.com.cn/help/img/idea/2026.2/app.expui.gutter.run.svg)). When you select the rule and click the Check... button, the Rule debugger dialog opens. You can see the result in the Generated pattern: field. The resulted expression in the Generated pattern: field must match the desired Target table.column: pattern.

![Create rules for virtual foreign keys](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_create_rules_for_virtual_foreign_keys.png)

Procedure: Debug rules for virtual foreign keys

1. Open settings (`Ctrl+Alt+S` (Windows), `⌘ Comma` (macOS), `⌘ Comma` (IntelliJ IDEA Classic (macOS)), `⌘ Comma` (macOS System Shortcuts), `Ctrl+Alt+S` (XWin), `Ctrl+Alt+S` (GNOME), `Ctrl+Alt+S` (KDE), `Ctrl+Alt+S` (Emacs), `Ctrl+Alt+S` (Sublime Text), `⌘ Comma` (Sublime Text (macOS)), `Ctrl+Alt+S` (NetBeans), `Ctrl+Alt+S` (Visual Studio), `⌘ Comma` (Visual Studio (macOS)), `Ctrl+Alt+S` (Eclipse), `⌘ Comma` (Eclipse (macOS))) and navigate to `Database | Other`.

2. Locate the Virtual Foreign Key section.

3. Select the rule and click the Check... button (![the Check button](https://resources.jetbrains.com.cn/help/img/idea/2026.2/app.expui.gutter.run.svg)).

4. In the Rule debugger dialog, fill the following fields:

* Column pattern:: the regular expression that will match a column name that you want to use as a virtual foreign key. For example, to describe columns that use the `_id` postfix, use the `(.*)_(?i)id` regular expression. This regular expression will find columns like `visitor_id` or `visitor_Id`, and capture `visitors` as the first capturing group (`$1`).

* Target column pattern:: the replacement pattern that uses the match from the Column pattern: expression and is interpreted as a regular expression. You can see the result in the Generated pattern: field. Note that the result appears only when you give an example of a column name in the Source column: field. For example, we can use the captured group (`$1`) from the Column pattern: expression, add a dot (`.`) and `id`. This expression will generate `visitor.id` reference that we can use as a primary key. Note: the Target column pattern: replacement pattern is used to generate a regular expression pattern in the Generate pattern field with captured groups. Ensure that you double-escaped symbols that are translated literally like the dot in the example expression (`$1\\.(?i)id`).

* Source column:: an example of a column name that you want to use as a virtual foreign key.

* Generated pattern:: a generated read-only regular expression pattern that is matched to the Target table.column: name. Generated pattern: is a result that is generated by the replacement pattern in Target column pattern:.

* Target table.column:: an example of the table and column name that will be used as a primary key for the virtual foreign key in Source column:.

![Debug rules for virtual foreign keys](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_rule_debugger_for_virtual_foreign_keys.png)

Procedure: Show virtual foreign keys in code completion

IntelliJ IDEA can generate you a list of possible code completion suggestions for `JOIN` statements. The code completion list includes suggestions of columns from other tables that have the same names as columns from the table in the `JOIN` statement.

For example, consider that the `actor` table has the following columns: `actor_id`, `first_name`, `last_name`, `last_update`. In the list of suggestions, you will see possible completions with names of these columns that are presented in other tables.

1. Open settings by pressing `Ctrl+Alt+S` (Windows), `⌘ Comma` (macOS), `⌘ Comma` (IntelliJ IDEA Classic (macOS)), `⌘ Comma` (macOS System Shortcuts), `Ctrl+Alt+S` (XWin), `Ctrl+Alt+S` (GNOME), `Ctrl+Alt+S` (KDE), `Ctrl+Alt+S` (Emacs), `Ctrl+Alt+S` (Sublime Text), `⌘ Comma` (Sublime Text (macOS)), `Ctrl+Alt+S` (NetBeans), `Ctrl+Alt+S` (Visual Studio), `⌘ Comma` (Visual Studio (macOS)), `Ctrl+Alt+S` (Eclipse), `⌘ Comma` (Eclipse (macOS)) and navigate to `Editor | General | Code Completion | Popup`.

2. Scroll down to the SQL  section, select Suggest non-strict foreign keys based on the name matching.

Checkbox selected:

![Suggest Non Strict Foreign Keys Based On The Name Matching is On](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_suggest_non_strict_foreign_keys_based_on_the_name_matching_on.png)

Checkbox cleared:

![Suggest Non Strict Foreign Keys Based On The Name Matching is Off](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_suggest_non_strict_foreign_keys_based_on_the_name_matching_off.png)