When I check the objectproperty(object_id,'CnstIsNotTrusted'), I get a value of zero, which I presume means the constraint is trusted. I even ran the "CHECK check" alter statement and saw no changes. I do not see anything different about these foreign keys, except they are referencing the base table of a view.

Here is the view that is created, which just renames the DimGenericDate.GenericDateKey to ApplicationDateKey. Following that is the SQL used when ApplicationDateKey was added to the fact table, including the addition of the foreign key constraint.

CREATE view CustomFinal.DimApplicationDate

as

select

GenericDateKey as ApplicationDateKey

,DaysFromZero

,FullDate

,DateName

,DayOfWeek

,DayNameOfWeek

from CustomFinal.DimGenericDate

alter table Student.FactApplications

add ApplicationDateKey int not null default -1,

constraint fk_FactApplications_ApplicationDateKey

foreign key (ApplicationDateKey)

references Custom.DimGenericDate (GenericDateKey)

While coming up with all this code, I figured something else out as well. Assuming I am not using anything from DimApplicationDate, if my SELECT does a JOIN to DimApplicationDate on ApplicationDateKey, then the join to DImGenericDate is NOT eliminated. If I join to DimGenericDate on GenericDateKey = ApplicationDateKey, then the join IS eliminated.

For some reason, the join will not be eliminated when joining to a view.