Primary and Foreign Keys

Data Warehouse does not enforce the uniqueness of primary keys. However, a non-unique value in a primary key column causes errors in the following situations:

• During the load, if data is loaded into a table that has a pre-joined projection

• In join queries at query time, if there is not exactly one dimension row that matches each foreign key value

  To ensure the uniqueness of your primary keys, use staging tables and the MERGE command (see Merging Data Using Staging Tables). If you want to store a version history in your table, the identifier of the source entity should be neither declared as a PRIMARY KEY nor referenced by a FOREIGN KEY column.

Similarly, the referential integrity declared by a foreign key constraint is not enforced during the data load, unless there is a pre-join projection. However, it may result in a constraint validation error later if a join query is processed or a new pre-join projection is created.