SQL Server: View to retain the foreign keys of the table?

Question

I have a table "Scholars", that has many foreign keys to look-up tables such as Courses and Colleges. When I create a view on that table to return a subset of the Scholars (eg those who are still living), the view doesn't seem to have the same foreign keys as the table. Am I quite new to SQL Server and not sure if I am doing something wrong or if that is simply how it is.

The reason I am asking, is because our clients use Report Builder 2 to create reports and when they create their queries using the query designer, if they use the view, query designer does not automatically hook up the LivingScholars view, say, to the Colleges table.

Thanks for any help, Robin

Answer 1

I'm sorry if I don't get it... but what do you mean by "view foreign keys"? When you create you view you have to create it like

SELECT     s.*
           , additional cols
FROM       Scholars s
JOIN       Courses c
ON         s.CourceID = c.ID
....
and so on

Answer 2

What you are seeing is normal.

Views can't have foreign keys. It's helpful to think of a view as a block of SQL that gets "cut and pasted" by SQL server into your queries every time you use it.

So if you have fields which you need from other tables, in this scenario, it's often best to just bring them into the view itself. You won't suffer any speed decrease for this. Just make a very wide view which your users will report off of. It'll be easier for your report writers, and as I said, you likely won't suffer any performance decrease if you're joining to tables that have foreign key references to the tables which underlie the view.

You may want to make a couple of views, one for each major type of query scenario which you expect your report writers to encounter.