Chartio Best Practice: Foreign Keys in Chartio

Foreign keys are the way to use two different tables from the same schema, together. They allow you to merge two tables by using join paths that can be set in multiple different ways using the Chartio data analytics product, including the option to use foreign keys to join two tables from different schemas together.

This post will highlight the three ways to use foreign keys in Chartio. This can be done in the schema page of your data source’s settings (schema definition), setting foreign keys in Visual SQL, or using columns in Chartio’s data pipeline to join between datasets.

1. Setting Foreign Keys in the Schema Page

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. To find this page, you’ll need to navigate to your data source’s schema page via the data sources tab > your data source > schema tab.

In this example, we want to use the Activity and User tables together in a chart. We’ll join the User ID column in the Users table to the User ID column in the Activity table. First, let’s select the Activity table, and click the User ID column to edit.

You’ll see a section labeled Foreign Key. From the first dropdown select the Users table, and then select User Id from the second dropdown. When finished, click Save to save your changes.

Chartio will now be able to auto-generate join paths between these two tables from Interactive Mode. By default, all join relationships generated in Interactive Mode are inner joins. It is possible to set a custom join type per foreign key, and to do that 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.

Chartio now has the ability to suggest foreign keys in your schema if you’re unsure of which foreign keys to place between tables. If this is the case, we’d recommend testing out the “Connect Tables” feature that can auto-detect possible foreign key relationships.

2. Setting Foreign Keys in Visual SQL

Chartio gives you the ability to set foreign keys in Visual SQL by clicking on the key icon located on the far right of the table name.

This brings up a page where you’ll be given a description and an example of what a foreign key is. To actually set a foreign key, click the Add Another Foreign Key button.

Two field boxes populate, the first being the tables we’re setting foreign keys between, the other field box is the columns we’re using as foreign keys. We’ll use our last example again where we want to use the Activity and Users tables together in a chart. We’ll join the User ID column in the Users table to the User ID column in the Activity table. Here is how we would set these foreign keys in Visual SQL.

3. Using Columns as Foreign Keys to Merge Datasets

If you’d like to join two or more datasets from different data sources in Visual SQL, you’ll have to access Chartio’s data pipeline merge step. Merging datasets allows for powerful post-processing and calculations using Chartio’s Visual SQL. For every join option except Union and Cross Join, you can choose how many columns you would like to join the datasets on.

We’ll use our example of joining the User ID column in the Users table to the User ID column in the Activity table.

Our dataset for the Activity table:

Our dataset for the Users table:

The order in which your columns are displayed in each dataset matter, as you’ll be joining on the first n columns. Since the columns in the dimensions field box come first and we’re joining on User Id for both tables, we need to have this show up as our first column. Once our columns are specified in the same order between both datasets, we can choose the merge type, then hit Apply & Close to carry out the merge:

This third method of using foreign keys in Chartio should only be reserved for datasets from different data sources, as the other two methods optimize your overall time in Chartio.