For a definition of Foreign keys, see our documentation.
Foreign keys are a key component to our Visual SQL. Without them, you won’t be able to pull data from multiple tables in a single dataset. We use foreign keys to automatically determine how tables should be joined, enabling seamless blending of data across tables.
There are two ways to add foreign keys in Chartio:
- On the Schema tab of a datasource
- In the chart editor
To highlight the two methods, we’ll use an example of joining the Activity and User tables in the Chartio Demo Source (also called “SaaS Company Demo Data”).
Foreign keys in the Schema tab
We want to use the Activity and Users tables together in a chart. To accomplish this, we’ll need to add a foreign key between the two tables to tell Chartio how to join them together. We’ll join the User ID column in the Users table to the User ID column in the Activity table.
We’ll navigate to our data source’s Schema page via Settings > Data Sources > our data source > Schema tab.
First select the Activity table, and click the User ID column to edit it.
You’ll see a section labeled Foreign Key. From the first dropdown we’ll 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, using the User ID column to link them together.
Foreign keys in the Chart Editor
You can also add foreign keys directly in the Chart Editor. By clicking the key Icon next to the Table name.
This brings up our foreign key wizard, which allows you to select the columns to connect.
For additional examples, view our Best Practices.