Foreign Keys

Foreign key graphic

Foreign keys tell the database how to join two or more tables together in the same query. In Chartio, foreign keys allow us to join tables together in Interactive Mode. For most database types, Chartio can import any foreign keys you have set up already. If Chartio is unable to import your foreign keys, or none are set up, you may need to define the foreign key relationships manually in the Schema, located in your Data Source’s settings. We also have a “Connect Tables” feature that can auto-detect possible foreign key relationships.

Connect Tables

Use the Connect Tables feature to automatically detect possible foreign keys. Review and approve the foreign key selections before they’re added to your Chartio schema. Note: foreign keys are only added to Chartio’s metadata, we don’t make any changes to the database itself.

Click the Connect Tables buttom on your schema

Adding a foreign key

Example

We want to make a chart to count the number of activities per username. Activity information is stored in the Activity table, and each activity has a User Id associated with it. However, Username is in the Users table. This means we need to link the Users and Activity tables together, through the User Id column. When we do that, we can get any related information from the Users table when we query the Activity table.

In our schema, we’ll click Activity to expand the Activity table. Then, we’ll click User Id to open the User Id column’s settings. In the Foreign Key setting, we’ll select the Users table, then select the Id column.

Select the foreign key

View detailed example

Foreign key joins

Once foreign key relationships are defined, the Data Explorer will automatically perform joins for you when using columns from related tables. The tables do not need to be directly related; Chartio will automatically find the closest path between a database’s tables to join the two that you’re using.

Setting custom join types

By default, all join relationships generated in Interactive Mode are explicit inner joins. However, it’s possible to set a custom join type per foreign key.

To customize the join type between two tables, first find the column in your schema and click it to open its settings. Adjust the join type in the dropdown below the Foreign Key setting. Click Save when finished.

Select the merge type

Now whenever the foreign key is used in Interactive Mode, Chartio will use the join type you’ve selected.