Best way to enforce this data-integrity constraint?

Question

I have 3 tables (lets call them Foo, Bar and Baz.

Tables:

Foo

• FooId

Bar

• BarId
• FooId

Baz

• BazId
• BarId
• AnotherValue

Obviously the foreign keys make it so that each Baz is associated with a Bar, and hence, associated with a Foo. Now I want to ensure that for each set of Baz with the same "AnotherValue" all the associated Foo's are unique

For instance, if I had

Foos (1, 2, 3)
Bars ((10, 1), (11, 1), (12, 1), (13, 2))
Bazs ((100, 10, "a"), (101, 10, "b"), (102, 13, "a"), (104, 11, "b"))

this should be blocked because Baz 104 and baz 101 both have AnotherValue "b" and Foo 1.

---

Options I have thought of (in order of my current preference)

Indexed View

I could create a view over these three tables and put a unique index on the two columns

Computed Column

Add FooId as a computed column to Baz. Then add an index on AnotherValue and FooId.

Check Constraints

I'm pretty sure this can be added and will work. I haven't used check constraints much, and I'm not sure if it is the best way to do this.

Trigger

This just seems ugly to me.

Answer 1

Just restating using different names:

Parent (ParentID) 
       (1, 2)
Child (ChildID, ParentID) 
       ((10, 1), (11, 1), (13, 2))
GrandChild (GCID, ChildID, GCAndParentVal) 
       ((100, 10, "a"), (101, 10, "b"), (102, 13, "a"), (104, 11, "b"))

Could you have that field in another table with the parentID and baz.anotherValue instead of including it in the table with the childID?

like this:

Parent (ParentID) 
       (1, 2)
Child (ChildID, ParentID) 
       ((10, 1), (11, 1), (13, 2))
GrandChild (GCID, ChildID, ...) 
       ((100, 10, ...), (101, 10, ...), (102, 13, ...), (104, 11, ...))
AnotherChildValue (ParentID, AnotherVal)
       ((1, "a"), (1, "b"), (2, "a"), (1, "b"))