When using ANSI JOIN syntax to create table joins with tables linked by foreign keys in the database,
JOIN completion can be used to automatically generate the necessary
join condition. Consider the following statement
SELECT ord.amount, ord.order_date, prod.name FROM orders ord JOIN product prod ON |
(the | denoting the location of the cursor).
When the cursor is located behind the
ON keyword and you select
→ , SQL Workbench/J will
retrieve the foreign key and corresponding primary key definitions between the tables
product. If such constraints exist, the corresponding condition will be generated and
written into the editor. After executing , the SQL statement will look like this:
SELECT ord.amount, ord.order_date, prod.name FROM orders ord JOIN product prod ON prod.id = ord.product_id
This feature requires the usage of the JOIN keyword. Joining tables in the
WHERE clause is not supported.
By default SQL Workbench/J tries to create a join condition on the table from the "previous"
FROM) clause. If no foreign key constraint is found linking the "current" and the "previous" table,
a popup window with all tables in the select statement that could be used for completion is displayed. This popup merely
looks at the tables in the statement, no test for foreign key constraints is done when displaying this list.
SQL Workbench/J supports the selection of foreign key values (i.e. the primary key values of the referenced table) in two different situations: while editing a result set and while writing a DML statement.
After starting to edit a cell, the context menu contains an item after the menu item has been invoked. If no foreign key could be found, a message is displayed in the status bar.. Once this item is selected SQL Workbench/J will detect the table that the current column references. If a foreign key is detected a dialog window will be shown containing the data from the referenced table. For performance reasons the check if the current column is referencing another table is only done
This is only supported for result sets that are based on a single table.
By default the dialog will not load more than 150 rows from that table. The number of retrieved rows can be configured through the "Max. Rows" input field.
There are two ways to find the desired target row which can be selected using the radio buttons above the input field.
Applying a filter
This mode is intended for small lookup tables. All rows are loaded into memory and the rows are filtered immediately when typing. The typed value is searched in all columns of the result set. Clicking on the reload button will always re-retrieve all rows.
This mode is intended for large tables where not all rows can be loaded into memory. After entering a search term and hitting the ENTER key (or clicking on the reload button), a SQL statement to retrieve the rows containing the search statement will be executed. The returned rows are then displayed.
Once you have selected the desired row, clicking thewill put the value(s) of the corresponding primary key column(s) into the currently edited row.
When invoking code-completion inside a DML (
UPDATE, DELETE, INSERT, SELECT) statement, an additional
(Select FK value) is available in the popup if the cursor is located inside the value assignment
or condition, e.g. in the following example:
update film_category set category_id = | where film_id = 42;
(the | denoting the location of the cursor).
When that menu item is selected, the statement is analyzed and if the column of the current expression is a foreign key to a different table, the lookup dialog will appear and will let you select the appropriate PK value from the referenced table.
Foreign key lookup for DML statement is currently only supported for single column primary keys.
To delete rows from the result set including all dependent rows, choose
DELETE statements to delete the dependent rows, before
DELETE for the selected row(s).
might take some time to detect all foreign key dependencies for the current update table. During this time a message will be displayed in the status bar. The selected row(s) will not be removed from the result set until the dependency check has finished.
Note that the generated SQL statements to delete the dependent rows will only be shown if you have enabled the preview of generated DML statements in the options dialog
You can also generate a script to delete the selected and all depending rows through→ . This will not remove any rows from the current result set, but instead create and display a script that you can run at a later time.
If you want to generate a SQL script to delete all dependent rows, you can also use the SQL Workbench/J command WbGenerateDelete.