Basic operations with columns
A column is a piece of data that is stored by a table. This data belongs to a particular type. A column may include text, numbers, or pointers to files in the operating system. Some relational database systems allow columns to include more complex data types like whole documents, images, or video clips.
In RubyMine, you can create, delete, reorder, hide columns and perform other actions.
Add a column
Right-click a table and select.
Specify a column name and settings.
Delete a column
Right-click a column and select Drop. Alternatively, press N/A.
(Optional) Click the Settings icon () to configure the drop process.
Modify a column
In the Database tool window, right-click a column and select Modify Column.
In the Modify table dialog, specify the column settings that you need.
To reorder columns, use drag-and-drop for the corresponding cells in the header row.
To hide a column, right-click the corresponding header cell and select Hide column.
Alternatively, you can right-click any of the cells in the header row and select Column List Ctrl+F12. In the Column List window, select a column name and press Space. You can hide all columns. To search through the column list, just start typing a column name in the Column List window.
Select the hidden column (a strikethrough column name) and press Space.
To sort table data in a column, click the cell in the header row.
Indicates that the data is not sorted in this column. The initial state of the sorting marker.
The data is sorted in the ascending order. The number to the right of the marker (1 on the picture) is the sorting level. You can sort by more than one column. In such cases, different columns will have different sorting levels.
The data is sorted in the descending order.
Inject a language for a column
You can assign a formal language (such as HTML, CSS, XML, RegExp, etc.) for the whole column. So, when you have a piece of code inside the cell that belongs to the column, you can use code generation, code completion, and other features IDE features.
To inject a language for the whole column, right-click the corresponding header cell, and select Edit As. In the list of supported languages, select the language that you want to inject.
A database index is a structure that is used to speed up locating and accessing operations in a database table. By using indexes, you decrease the number of disk accesses that are required when a query is processed. You can create indexes for one or more columns of a database table.
Create an index
In the Database tool window (), expand the data source tree until the node of a column.
Right-click a table or a column and select.
In the Columns pane, click the Add button ().
In the Name field, specify the name of the column that you want to add to the index.
Foreign key relationships specify how tables relate to each other and indicate relationships between tables. RubyMine 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 RubyMine, 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, RubyMine 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.
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.
Create a foreign key in a database
In the Database tool window ( ), expand the data source tree until the node of a child table.
Right-click a child table and select.
In the Target table pane, specify the name of the target table.
In the Columns pane, click the Add button .
In the From field, specify the name of the column in the child table.
In the To field, specify the name of the column in the target table.
Create a virtual foreign key
Click the table relation in the ON clause and press Alt+Enter.
Select Store table relation.
The relation is saved in external-data.xml. You can see the path to the XML document in the data source settings (Shift+Enter) on the Options tab.
Create rules for virtual foreign keys
You can use regular expressions to create a rule according to which RubyMine will point a column in one table to a column in another table.
Open settings (Ctrl+Alt+S) and navigate to.
Scroll to the SQL 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 see the result in the Generated pattern field. The resulted expression in the Generated pattern field must match the desired
You can check your rules by using the Check button (). When you click the Check button, the Rule debugger dialog opens.
Debug rules for virtual foreign keys
Open settings (Ctrl+Alt+S) and navigate to.
Scroll to the SQL section.
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 like
visitor_Id, and capture
visitorsas the first capturing group (
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 (
id. This expression will generate
visitor.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 (
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.