In this lesson, you will define primary and foreign keys on target tables to use later in a join. To do this lesson, you must have defined the dimension tables and fact table in Defining the rest of the tables for the star schema (optional).
In each target table, you will select a column that can be used to uniquely identify rows in that table. This will be the table's primary key. The column that you select as a primary key must have the following qualities:
For example, the CITY_ID column in the LOOKUP_MARKET table (created in Lesson 8, Defining data transformation and movement) is a good candidate for designation as a primary key. Because each city needs an identifier, no two cities can have the same identifier, and identifiers are unlikely to change.
Defining a primary key for a table is highly recommended because uniquely identifying each row speeds up row access.
You use foreign keys to define relationships between tables. In a star schema, a foreign key defines the relationship between the fact table and its associated dimension tables. The primary key of the dimension table has a corresponding foreign key in the fact table. The foreign key requires that all the values of a given column in the fact table also exist in the dimension table. For example, the CITY_ID column of the FACT_TABLE might have a foreign key defined on the CITY_ID column of the LOOKUP_MARKET dimension table. This means that a row cannot exist in the FACT_TABLE unless the CITY_ID exists in the LOOKUP_MARKET table.
In this lesson, you will define primary keys on the four target tables that you created in Lesson 8, Defining data transformation and movement: LOOKUP_MARKET, LOOKUP_TIME, LOOKUP_PRODUCT, and LOOKUP_SCENARIO. You will define corresponding foreign keys in the FACT_TABLE target table.