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:
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.
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, click the Options tab and see the Virtual objects and attributes field.
Virtual foreign keys ( 
) 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 chapter of Database tool window topic. For the table column icons, refer to the Possible icon combinations for columns chapter.
Create a virtual foreign key
In the Database tool window ( ) , expand the data source tree until the nodes of tables.
Right-click the table node and select .
In the Modify dialog that opens, enter the name of your virtual foreign key in the Name field.
In the Target Table pane, specify the name of the target table.
In the Columns pane, click the Add button (
).In the Column Name field, specify the name of the column in the child table.
In the Target Name field, specify the name of the column in the target table.
Click OK to add your virtual foreign key.
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.
Click the table relation in the
ONclause and press Alt+Enter.Select Store table relation.
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 rules for virtual foreign keys
You can use regular expressions to create a rule according to which DataSpell will point a column in one table to a column in another table.
Open settings (Ctrl+Alt+S) and navigate to .
Locate the Virtual Foreign Key section.
In the table, click the Add button (
).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.
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 (
). 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.
Debug rules for virtual foreign keys
Open settings (Ctrl+Alt+S) and navigate to .
Locate the Virtual Foreign Key section.
Select the rule and click the Check button (
).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
_idpostfix, use the(.*)_(?i)idregular expression. This regular expression will find columns likevisitor_idorvisitor_Id, and capturevisitorsas 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 (.) andid. This expression will generatevisitor.idreference 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.
Show virtual foreign keys in code completion
DataSpell 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.
Open settings by pressing Ctrl+Alt+S and navigate to .
Scroll down to the SQL section, select Suggest non-strict foreign keys based on the name matching.
