asked
How do I create a constraint on a view?
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 view can't be part of a foreign key reference. The date keys in your table will need to reference the date table directly
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.
The answer to question 1 is Yes. SSIS has absolutely no problem with that--we do it for our main fact tables. Question 2 is also a Yes. You could even still use the OrderDate, ShipDate, etc. views in your SSIS lookup--in the end, that just gets translated into selecting the query which defines the view (which goes down to your base Date table). This is perhaps more my own opinion than anything else, but I wouldn't even bother with the views for the ETL process--I would build my queries right off of the Date dimension. The views can be nice when building SSAS cubes because they give business users a bit more context (and, if I remember correctly, you either need to have a view defined or created what is essentially a view within the SSAS cube model).
Thanks Kevin! I understand your two options. You're right, a normal view is just a saved query, so we cannot have integrity constraints on a view. I think one of your two options will work, and if I explain a bit more, maybe you can assist me a bit more. I have six date fields in my fact table, e.g., Order_Date, Ship_Date, etc.etc. When a new record comes from my transaction system with all these dates, my ETL process (Using SSIS), uses the Date table to look up the surrogate key for each of these 6 dates. It then inserts those surrogate keys into the 6 dateKeys in my Fact table. So if I use your option 1, and place foreign key constraints onto the Date Table, I will have six Foreign keys in my fact table referencing the same Primary key in my Date Table. Question 1 - Is it ok to have 6 date key FKs from Fact table like Order_Date_Key, Ship_Date_Key, reference one Date_Key in the date table it like this? Questions 2 - When my ETL process runs, it will use Date Table as a Look Up table to find the 6 surrogate keys for the 6 dates coming in. Will that work? (I guess I haven't tried this yet). Thanks in advance!
