Monday, February 22, 2010

Why can't I ...

... set up a foreign key relationship in SQL Server 2008 where the parent is a view with a unique index set up over it?

Unless I'm much mistaken, you can't do this.

You can achieve the same result with triggers, but:
  1. A lot of people hate triggers;
  2. You have to code the things, which introduces possible errors, depending on how sharp the coder is and how well he or she knows trigger behavior.  Foreign key relationships are hard to get wrong.
  3. ETA:  You also have to set up triggers on both sides of the relationship, if you want to constrain both tables.   With a foreign key, the mutual constraining is automatic. 
Why do I want to do this?  Well, I have a table where a certain field can be any value from the Id column of Table 2, provided [Table 2].TypeId = 3.  I'd love it if the database would enforce this.  But unless I can persuade folks that a trigger is an acceptable choice in this situation, it'll have to be done in code.

posted by Ann at 2:12 PM

2 Comments:

Blogger DonSchenck said...

Without knowing the details, methinks a different db design would yield the functionality you desire.

That said ... you question is a good one.

2/22/2010 4:21 PM  
Blogger Ann said...

I can think of designs that would make the functionality I want possible, but it's a bit like stretching a too-small sheet over a too-large mattress: if you get the final corner covered, it's only because some other corner has come untucked. I could achieve true referential integrity with some of the ideas I've come up with, but only at the cost of adding complexity in even less welcome places.

I think the trigger is probably my best bet, given the size and maturity of this system. The impact (both on the system and in developer/QA hours) is probably the smallest with that option.

Thanks!

2/22/2010 6:16 PM  

Post a Comment

<< Home