Foreign Keys against Views

January 5, 2010

Foreign Keys are great. For those of you who read some of my other posts, or heard me present on this material before, a foreign key can really help the Query Optimizer simplify out your query, because it knows that it must be able to find a match in an equality join (unless the field allows NULLs, which is a whole nother story).

I also blogged recently about the fact that a foreign key doesn’t have to reference the primary key of a table. Turns out it can reference any candidate key, that is, one that has a unique index on it. This presents all kinds of interesting design ideas, such as avoiding joins by storing a different field (such as the username, rather than userid, if the username is the one thing you keep looking up to display at the top of the webpage). Referential integrity can still be maintained happily using an index seek (just a slightly larger index, but quick nonetheless), but the benefit could be huge if many queries no longer need to do join to get that info.

But I found an interesting question on Stack Overflow, which was asking about whether a foreign key could reference a view. He was trying to do it for the purpose of data integrity — which would probably require an expensive trigger. But let’s talk about the Query Optimizer, which I think is another useful reason to have a foreign key hook into a view.

As a view is only a stored sub-query, it’s a strange request, but one that is definitely interesting. A view can be instantiated as an indexed view, so I thought there was definitely potential in his question.

Unfortunately I had no luck trying to help him.

My first thought was “Well, you’re going to need a unique index on the view — well that’s doable — a Clustered Index on an Indexed View.”, but you get an error saying that the foreign key needs to reference a user table. A user table? So it can’t even reference a system table. But I guess that’s fair enough these days, since we don’t really have system tables any more (they’re all system views, referencing underlying functions and the like).

My next thought was whether or not you could recreate something similar using a filtered index. After all, a filtered index satisfies many of the scenarios that indexed views often address. They can be unique for example, so you might use an indexed view to make sure that once a particular value is reached in a Status field, some other field must be unique. In SQL Server 2008 it’s no problem to create a unique index and provide a WHERE clause for it. But unfortunately when you create a foreign key, you indicate the table you’re referencing, not the index. I tried using a check constraint, to make sure that the foreign column could only contain values that were in the unique filtered index, but SQL just doesn’t consider that a filtered index is available for referencing by a foreign key.

If you know a way to help this guy, follow the link and provide an answer. In a couple of days’ time, the answer with the most votes will be automatically marked as the answer by the Stack Overflow system, which won’t help him much. He understands that using views doesn’t negate the FK constraints on the underlying tables, and I hope he can still work out a way of doing it without triggers, but at the end of the day, this other answer of mine, where I eventually say “Use triggers on the tables which contribute to your view” is probably the unfortunate truth.

This Post Has 6 Comments

  1. David Gardiner

    Unless something has changed recently, Stack Overflow doesn’t automatically mark an answer as correct.

    The questioner chooses the answer they like the best – and maybe a user with enough reputation can do it too.

    -dave

  2. robfarley

    Actually, on Stack Overflow, if there is a bounty put on a question, then the question with the most votes gets auto-accepted after seven days (if it has at least two). You can read this on the FAQ page there at http://stackoverflow.com/faq

  3. BuschnicK

    I’m the guy who posed the original question. Thanks for trying to understand my problem and testing the various scenarious Rob. Can I split the reward on Stackoverflow somehow? Because the currently top-voted answer by Damir deserves respect for the effort he put into it as well.

    Anyways, I haven’t found a solution yet and will probably go with manually filtering/joining (as per the most common suggestion in the StackOverflow thread) the views so that the data is consistent at all times. Still seems like an oversight in SQLServer to me.

    BTW, what I’m trying to archive with the whole setup is to have one underlying dataset, the “truth”, and several different interpretations/views on it. This should accomodate users who don’t have enough rights to view the full set or just want to work on a subset.

  4. robfarley

    I’m not too interested in the StackOverflow points, that’s fine.

    I think the ‘oversight’ applies only when you consider that you can’t use a unique index that’s on an indexed view for a FK. I consider that an indexed view essentially becomes a user-table that is maintained through changes to other tables – trigger-like if you will.

    And I think a FK should be created to reference an index, not a table. Then you could easily reference the index that’s on the view, and it shouldn’t really care. An index is an index, regardless of what it’s on.

    I do like your eagerness to use views. At some point soon, the recording of my “Designing for Simplification” talk from SQLBits should become available, at which point you should check it out and see how views can really enhance the developer experience.

  5. TroyK

    Unfortunately, SQL doesn’t support this feature. It should, as a view and a base table are treated the same (i.e., simply as relations) in the relational model.

    I gather from the discussion here that the desire is for the declaration to support some referential integrity rules. If that’s the case, please check out the following two articles from a series I wrote. They explain how to convert a couple of different types of business rules (that could easily be supported by an FK to a view) into T-SQL implementations:

    http://www.sqlservercentral.com/articles/Data+Modeling/61528/
    http://www.sqlservercentral.com/articles/Data+Modeling/61529/

    HTH,
    TroyK

  6. Dako

    That would be a great feature. I’ve missed it several times, where it would help ensuring business rules in the database. But I agree, seems there’s no way to do it in SQL Server so far. Nevertheless, I hope to be wrong 🙂

Leave a Reply

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.

Search