Database Reference
In-Depth Information
Adding Primary Key Constraints
You should include primary key constraints in all of your dimension and fact tables because they keep your
data ordered and free of duplicate values. In most dimension tables, you add a primary key constraint to its
single key column. But in fact tables, you add a primary key constraint to multiple key columns, because it is the
combination of key values that distinguishes one row from another. When a primary key constraint is associated
with multiple columns, these columns form a
composite primary key
.
As an example, there are two key columns in the FactWeather table, the Date and EventKey, both of which
refer to dimensional tables. The other two columns in the table are MaxTempF and MinTempF, both of which are
measure columns. The multiple dimensional key columns form a composite primary key for a fact table.
The code in Listing 2-4 creates a primary key constraint on the DimEvents and FactWeather tables.
Adding the constraint to the table identifies which column or columns are part of the primary key and enforces
uniqueness of values across these columns.
Listing 2-4.
Adding the Primary Keys
-- Step 6) Create Primary Keys on all tables
Alter Table DimEvents
Add Constraint
PK_DimEvents
Primary Key
( [EventKey] )
Go
Alter Table FactWeather
Add Constraint
PK_FactWeathers
Primary Key
( [Date], [EventKey] )
Go
Looking back at Figure
2-11
, you can see the primary key icons are on both the Date and EventKey columns,
which indicates that both columns are part of a composite primary key. Look for these icons, or something
similar, in any database diagram you review.
Adding Foreign Key Constraints
Notice in Figure
2-11
that both the fact table and the dimension table have a column called EventKey. In the fact
table, the EventKey column forms a foreign key relationship back to the DimEvents dimensional table. The code
in Listing 2-5 adds a foreign key constraint to enforce this relationship and will not allow you to enter key values
in the fact table if they do not first exist in the dimension table. For instance, if you try to insert an EventKey value
of 42 to the fact table, the constraint would check to see whether an EventKey value of 42 exists in the dimension
table. If not, the database engine generates an error message and the insert fails!
Listing 2-5.
Adding the Foreign Keys
-- Step 7) Create Foreign Keys on all tables
Alter Table FactWeather
Add Constraint
FK_FactWeather_DimEvents
Foreign Key
( [EventKey] )
References
dbo.DimEvents ( [EventKey] )
Go
■
Many exercises in this topic are written in a way that assumes you have some familiarity with SQL
programming. we have tried to make our code simple enough for all levels of developers, but some of this subject
matter may be difficult if you have never used SQL before. To help you become more familiar with this language, we
Note
