To all Gurus: I have 6 Views with the same underlying table. Underlying table is Date table and the 6 views are Order_Date, Ship_Date, etc. I also have a Fact table with these 6 Foreign Keys that reference the 6 Views. I need to create Primary Key and Foreign Key constraints between this Fact table and the 6 Views. I'm not being able to do so because: 1. I can't define a Primary Key for a view in SQL Server 2008 2. I am not able to define a constraint in a SQL Server view (I use SQL Server Management Studio, and I don't see any way to create a constraint in a View). Please advise how to create these 6 constraints between the PKs of the views and the Fact Table FKs. Thanks in advance!
A normal view is just a saved query, nothing more. The data isn't saved on the disk--it isn't materialized. Thus, you cannot have integrity constraints (check constraints, foreign keys, or primary keys) on a view: there's nothing to constrain. Even an index view--which is materialized--does not allow for constraints. It sounds like you are using this for a data warehouse. If you are, there are two options available: either put the foreign key constraint onto the Date table (as @KenJ mentions), or don't have any constraints at all. In a data warehouse, it isn't necessarily a bad thing not to have constraints so long as you only have one way of moving data in and never add, remove, or update data outside of the single process. But if you're doing this in the guise of an OLTP database, I'd say just join to the Date table, and if you need additional constraints (like having an OrderDate less than ShipDate), add check constraints on the fact table itself.