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?

Visser

my reaction is - remove that extra link from C to A - have you proven there is a performance issue? or is this just to simplify some query somewhere? – Mycobacterium

Mainly meant for easy/quick retrieval. C is a detail record of B. In most cases I just want all details in C referenced by A's key. – Visser

How about a real-example? The performance problem may not exist at all and by using natural keys you may be able to join directly anyway. – Lulita

Answer 1

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).

Lulita

Answer 2

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.

Trahan

Answer 3

Denormalization for the purposes of improving performance is pretty common, especially if you have evidence showing its value. I assume you have good reasons for doing so, so I won't address that.

Have you thought of simply having an insert trigger on C which sets the column referencing table A based on a lookup in table B? You may also need update triggers on C and B to ensure it is always in sync. This would ensure the column in table C which references table A is always correct, even if it isn't "enforced" by an actual constraint.

U

I work in database with a very high transaction volume so performance is critical. I may test out your idea and see what performance impact triggers will have, Thanks! – Visser

Answer 4

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.

You can have table B have a dual primary key (A's key, and then say an identity), and then use that to link to C. This won't allow you to have a null foreign key reference on B though, but foreign keys aren't allowed to be null anyway.

Really if you have indexes etc. set properly, there isn't a real need to push A's key to C. Joining it to table B to get A's key won't be that much of a performance hit (like almost none).

Caneghem

Good Idea! That was my first thought before I found out we won't always know A's key. Hum...Maybe I'll try the Join. B typically isn't used a whole lot which is why I have A's key in table C. 90% of the time I just need details out of C using A's key. – Visser

Answer 5

Looks like the common ues case for you is you have A's key and you need all the matching rows in C. In which case the following query should be fast:

select C.* 
from B
join C on C.Bid = B.Bid
where C.Aid = <value>

with proper indexes this should be just as fast as if you have Aid on C because they will both require an index scan followed by joining that result to the C table.

Steamer