Understanding the role of primary keys and foreign keys
Keys are very important in a relational database because they help create a relationship between two tables and ensure that each record in a table can be identified in a unique way. When we connect the related tables, the version of the connecting columns in the dimension tables is called the primary key while the version in the fact table is called the foreign key.
In a relational database, the primary key helps us identify a single row in a table. All primary keys must be unique in the columns where they sit. Foreign keys help connect our dimension and fact tables and, more importantly, make our fact tables accessible in our calculations. Foreign keys are typically not unique.
Going back to our mock view of our relational database, we can add primary keys and foreign keys, as shown here:
Figure 2.8: Assigning primary keys and foreign keys
In our data model, these primary keys will be linked...