DataSpell 2023.2 Help

Foreign keys

Foreign key relationships specify how tables relate to each other and indicate relationships between tables. DataSpell 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 DataSpell, you can use explicit foreign keys or create virtual foreign keys.

Foreign keys can be seen in the Database tool window. You can see a reference on node and object icons in the Data sources and their elements chapter of Database tool window topic.

For the table column icons, refer to Possible icon combinations for columns.

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:

CREATE TABLE visitor ( id int NOT NULL, activity_id int NOT NULL, PRIMARY KEY (id), FOREIGN KEY (activity_id) REFERENCES activity(activity_id) );

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, DataSpell 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.

Explicit foreign keys

Create a foreign key

  1. In the Database tool window ( Window | Tool Windows | Database) , expand the data source tree until the nodes of tables.

  2. Right-click the table node and select New | Foreign Key.

  3. In the Modify dialog that opens, enter the name of your foreign key in the Name field.

  4. In the Target Table pane, specify the name of the target table.

  5. In the Columns pane, click the Add button (the Add button).

  6. In the Column Name field, specify the name of the column in the child table.

  7. In the Target Name field, specify the name of the column in the target table.

  8. In the Preview pane, you can view and change the generated SQL code.

  9. Click OK.

Create a foreign key

Productivity tips

Modify templates for generated index and key names

When you create indexes, and primary and foreign key constraints, their default names are generated according to corresponding templates. For a primary key, for example, the template is {table}_{columns}_pk.

  • To view and modify these templates, open the settings Control+Alt+S and navigate to Editor | Code Style | SQL | General. Click the Code Generation tab.

    The templates can contain variables and text. When you generate a name, the specified text is reproduced literally. For example, when you apply the {table}_pk template in the actor table, the generated name of the primary key will be actor_pk.

    To see information about variables and their usage, click a field and press Control+Q.

    {unique?u:} checks if the index is unique and inserts the corresponding sequence of characters. If the index is unique, the template generates a name with the sequence of characters specified between ? and :. For the {unique?u:} template, it is u. If the index is not unique, the sequence between : and } is inserted. For the {unique?u:} template, it is nothing.

    Example

    You have the persons table with columns FirstName and LastName. The {table}_{columns}_{unique?u:}index template generates the following name for the not unique index: persons_FirstName_LastName_index.

    Modify templates for generated index and key names

  • Right-click a column in a table and select Go To. In the Go To submenu, you can select to which type of related rows you want to navigate.

    • Referenced Rows: rows that are referenced by the current object.

    • Referencing Rows: rows that are referencing the current object.

    • All Related Rows: both referenced and referencing rows.

    Referenced and referencing data

    In the data editor, you can now select several values and navigate to the related data.

Last modified: 22 September 2023
Primary keys Indexes