Multiple column foreign key contraints

Question

I want to setup table constraints for the following scenario and I’m not sure how to do it or if it’s even possible in SQL Server 2005.

I have three tables A,B,C. C is a child of B. B will have a optional foreign key(may be null) referencing A. For performance reasons I also want table C to have the same foreign key reference to table A. The constraint on table C should be that C must reference its parent (B) and also have the same foreign key reference to A as its parent.

Anyone have any thoughts on how to do this?

Answer 1

I don't see a need to explicitly enforce the relationship from C to A. Simply follow the chain from C to B to A.

Answer 2

In general I do not see a specific reason to do this -- however, you did ask.

Thing to understand is that a relational model does not have to follow an OO model. This is a standard way to present Customer-Order-LineItem. Nothing wrong with this.

If I want to find all line-items belonging to a customer, I have to join via the Order table, similar to the OO dot-dot notation (Customer.Order.LineItem).

select * 
from Customer as c
join Order    as o on o.CustomerId = c.CustomerId
join LineItem as i on i.OrderId    = o.OrderId
where CustomerID = 7 ;

Suppose that I modify keys a bit, like:

The CustomerOrderId is an order sequence number for each customer (1,2,3 ...) and the CustomerOrderItemId is a line-item sequence number for each of the customer's orders (1,2,3 ...). Each one is easy to generate, as in

-- next CustomerOrderId
select coalesce(max(CustomerOrderId), 0) + 1
from  Order
where CustomerId = specific_customer_id;

-- next CustomerOrderItemId
select coalesce(max(CustomerOrderItemId), 0) + 1
from  LineItem
where CustomerId      = specific_customer_id
  and CustomerOrderId = specific_customer_order_id;

Now if I want to find line-items belonging to a customer (and some customer data), I can skip the Order table.

select * 
from Customer as c
join LineItem as i on i.CustomerId = c.CustomerId
where CustomerID = 7 ;

And If I do not need any specific data from the Customer table, no need to join at all. Compare this to the first example -- remember that getting line-items was the objective.

select * 
from LineItem
where CustomerID = 7 ;

So, with the relational model, by propagating (natural) keys, you do not have to always "stop at each station along a relationship path" in joins.

Which is better? Depends who you ask.

Hope you will be able to translate the underlying principle into your example -- I find it hard to work with generic (A,B,C).