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.
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.
Adding a foreign key
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.
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.
Now whenever the foreign key is used in Interactive Mode, Chartio will use the join type you’ve selected.
Take a look at the video below which explains foreign keys and goes over the different ways of adding them to your data sources in Chartio: