Once you have retrieved data from a table that has foreign key relations to other tables, you can navigate the relationship for specific rows in the result set. Select the rows for which you want to find the data in the related tables, then right click inside the result set. In the context menu two items are available:

Consider the following tables:

The context menu for the selected rows will give you the choice in which SQL tab you want the generated SELECT to be pasted. This is similar to the Put SELECT into feature in the table list of the DbExplorer.

Once you have obtained a result set from the table BASE, select (mark) the rows for which you want to retrieve the related rows, e.g. the one where id=1. Using Referencing rows → DETAIL SQL Workbench/J will create the following statement:

SELECT *
FROM DETAIL
WHERE base_id = 1;

The result of the generated statement will always be added to the existing results of the chosen SQL panel. By default the generated SQL statement will be appended to the text editor. If you don't want the generated statement to be appended to the editor, hold down the Ctrl key while selecting the desired menu item. In that case, the generated statement will only be written to the messages panel of the SQL tab. If the target tab contains an external file, the statement will never be appended to the editor's text.

To navigate from the child data to the "parent" data, use Referenced rows

The additional result tabs can be closed using Data → Close result

When using ANSI JOIN syntax to create table joins with tables linked by foreign keys in the database, the command 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 → JOIN completion, SQL Workbench/J will retrieve the foreign key and corresponding primary key definitions between the tables orders and product. If such constraints exist, the corresponding condition will be generated and written into the editor. After executing JOIN completion, 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" JOIN condition (or the 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.

You can configure this feature to generate a USING operator if the column names match. The case of the keywords in the generated condition is determined by the settings of the SQL Formatter.

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.

15.3.1. Editing foreign key values

After starting to edit a cell, the context menu contains an item Select FK value. 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 after the menu item has been invoked. If no foreign key could be found, a message is displayed in the status bar.

	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.

• Retrieving data

  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 the OK will put the value(s) of the corresponding primary key column(s) into the currently edited row.

update film_category
    set category_id = |
  where film_id = 42;

To delete rows from the result set including all dependent rows, choose Data → Delete With Dependencies. In this case SQL Workbench/J will analyze all foreign keys referencing the update table, and will generate the necessary DELETE statements to delete the dependent rows, before sending the DELETE for the selected row(s).

Delete With Dependencies 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 Data → Generate delete script. 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.