In the Rule debugger dialog, fill the following fields:Ĭolumn pattern: the regular expression that will match a column name that you want to use as a virtual foreign key.įor example, to describe columns that use the _id postfix, use the (.*)_(?i)id regular expression. Select the rule and click the Check button ( ). The resulted expression in the Generated pattern field must match the desired Target lumn pattern. You can see the result in the Generated pattern field. When you select the rule and click the Check button, the Rule debugger dialog opens. You can check your rules by using the Check button ( ). The replacement pattern uses the match from the Column pattern expression and is interpreted as a regular expression. Scroll to the SQL section and JOIN clauses subsection.ĭouble-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.ĭouble-click the Target column pattern cell and type the replacement pattern. Open settings ( Ctrl+Alt+S) and navigate to Editor | General | Code Completion. You can use regular expressions to create a rule according to which GoLand will point a column in one table to a column in another table. 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. You can select other name for the XML file and other place to store this file. The relation is saved in external-data.xml. Create a virtual foreign keyĬlick the table relation in the ON clause and press Alt+Enter. 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. You can save this relation and use it later or configure rules for virtual foreign keys in settings by using regular expressions. 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. Let's assume that visitor_id is not defined as a foreign key in the database. SELECT * FROM activity JOIN visitor ON visitor_id = visitor.id Alternatively to foreign keys, virtual foreign keys are not defined in the database code. In this case, you can still create foreign key relations without changing your database code. 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. There are cases when you do not want to use explicitly-defined foreign keys. In the Target Name field, specify the name of the column in the target table. In the Column Name field, specify the name of the column in the child table. In the Columns pane, click the Add button ( ). In the Target Table pane, specify the name of the target table. Right-click a child table and select New | Foreign Key. In the Database tool window ( View | Tool Windows | Database), expand the data source tree until the node of a child table. In the following example, activity.activity_id is a primary key, while visitor.activity_id is a foreign key. If your database contains explicit foreign key relationships, GoLand automatically uses them in auto-completion, data navigation, and diagrams. The table that contains a candidate key is a referenced or target table. The table that contains a foreign key is a child table. FOREIGN KEY (activity_id) REFERENCES activity(activity_id)
0 Comments
Leave a Reply. |