Search code examples
sqldatabasessisdata-warehousefact-table

Should I add Foreign Key constraint when creating Fact Table in SQL?

The question is a bit naive. But when I learned, it's said that you should add both Primary Key and Foreign Key to Fact Table as below:

LearnedCourse

About the Primary Key in Fact Table, there're many posts on the internet about this and I've already got some clues. But about Foreign Key, when I did some research, I found out that when creating Fact Table on SQL, they never add Foreign Key constraint to any columns at all, which confuse me.

Here is what I found on IBM website (other websites are nearly the same, no FK constraint at all when creating Fact Table)

CREATE TABLE sales 
( 
customer_code  INTEGER,
district_code  SMALLINT,
time_code      INTEGER,
product_code   INTEGER,
units_sold     SMALLINT,
revenue        MONEY(8,2),
cost           MONEY(8,2),
net_profit     MONEY(8,2)
);

But I expect some FK constraints reference to Dimension Tables' Surrogate Key. I know that in SSIS we will look up the key anyways, but I'm still not sure should I add FK when creating Fact Table on SQL.

Solution

  • If your database has not-enforced foreign keys like SQL Server does then you absolutely should have foreign keys on your fact table. Then if you decide do turn of foreign key enforcement and manage data integrity in ETL you can make that tradeoff.

    If your data warehouse is very large and the cost of enforcing foreign keys is prohibitive, then you might omit them. But the general practice of omitting FKs in a data warehouse dates from the bad old days when a terabyte was a considered huge, and should be left behind.