Connect your tables with foreign keys

Foreign keys tell the database how to join two or more tables together in the same query. In Atlassian Analytics, foreign keys allow us to join tables together in visual mode queries. For most data source types, Atlassian Analytics can import any foreign keys you’ve set up already. If Atlassian Analytics 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 or use the “Connect tables” feature to auto-detect possible foreign key relationships in your schema.

In the example above, the “user_id” column in the “users” table is the primary key. The “user_id” column in the “activity” table refers to the “user_id” column of the “users” table; therefore, the “user_id” column in the “activity” table is a foreign key.

Connect tables

When possible foreign key relationships in your data source are detected, Atlassian Analytics can assist with setting up the foreign keys between tables with the “Connect tables” feature.

To set up multiple foreign keys at once:

1. Go to the Schema tab of the data source’s settings.

2. Select the schema you want to set up foreign keys for.

3. Select Connect tables (if enabled). A pop-up will appear, showing foreign key suggestions.

4. Review and approve foreign key suggestions by selecting the checkbox next to each one.

5. Select Save selected to add the approved suggestions to your Atlassian Analytics schema.

Add a foreign key

You can manually add a foreign key to your schema by editing the column’s properties. Learn how to edit a column.

To add a foreign key to a table:

1. Go to the Schema tab of the data source’s settings.

2. Expand the table containing the column you want to add a foreign key to.

3. Expand the properties of the column you want to use as a foreign key.

4. In the Foreign key property, select the schema (if there are multiple), table, and column of the primary key you want this foreign key to reference.

5. Select the type of join you want to use to connect the tables containing the foreign key and the primary key. Learn more about join types.

6. Select Save.

Foreign key setup example

Let’s go back to the example shown earlier, where we want to connect an “Activity” table and a “Users” table using foreign keys. We want to make a chart to count the number of activities per username. Activity data 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; we can do this using the “User Id” column. When we do that, we can get any related user data from the “Users” table when we query the “Activity” table in visual mode queries.

Without setting up foreign keys, we’d need to create separate queries—one for the “Activity” table and another for the “Users” table—then join those queries together. In Atlassian Analytics, setting up foreign keys enables you to use both these tables in a single visual mode query, auto-generating the join in the backend so you don’t have to do it manually.

To join these tables in our Atlassian Analytics schema, we’ll expand the “Activity” table to view a list of all its columns. Then, we’ll select User Id to open its column properties. In the Foreign key setting, we’ll select the Users table then select the User Id column.

Foreign key joins

Once foreign key relationships are defined, Atlassian Analytics automatically performs joins for you when using columns from related tables in visual mode queries. The tables don’t need to be directly related; Atlassian Analytics automatically finds the closest path to join the tables you’re using.

Setting custom join types

By default, all join relationships generated in visual mode queries are explicit inner joins; however, you can change the join type per foreign key.

To customize the join type between two tables:

1. Find the column in your schema.

2. Select the column to open its column properties.

3. Select the join type within the Foreign key settings of the column.

4. Select Save.

Visualize your schema

To see a visual representation of how your tables are connected in Atlassian Analytics:

1. Go to the Schema tab of the data source’s settings.

2. Select Visualize.

This generates a schema diagram, which uses arrows to show how tables are connected. An arrow will start at the foreign key and point to the primary key that it references.