多列外键约束 [英] Multiple column foreign key contraints

问题描述

我想为以下情况设置表约束，我不知道如何做，或者如果它在SQL Server 2005中甚至可能。

我有三个表A，B，C。 C是B的子节点.B将有一个可选的外键（可能为null）引用A.出于性能原因，我还希望表C具有与表A相同的外键引用。表C上的约束应该是C必须引用其父（B），并且具有与作为其父代的A相同的外键引用。

任何人都有任何想法如何做？

解决方案

一般来说，我没有看到具体的原因这样做 - 但是，你问。

要理解的是，关系模型不必遵循OO模型。
这是显示 Customer-Order-LineItem 的标准方法。没有什么错误。

如果我想查找属于某个客户的所有订单项，我必须通过 Order 表加入，类似（ Customer.Order.LineItem ）。

 从Customer中选择* 
为c 
在O.CustomerId = c.CustomerId 
上连接Order作为i在i.OrderId = o.OrderId 
上将LineItem作为i连接其中CustomerID = 7; 
  

假设我修改键有点，如：

CustomerOrderId 是每个客户的订单序列号（1,2,3 ...）， CustomerOrderItemId 每个客户订单的项目序列号（1,2,3 ...）。每个都很容易生成，如

   - 下一个CustomerOrderId 
 select coalesce（max（CustomerOrderId），0 ）+ 1 
来自Order 
其中CustomerId = specific_customer_id; 
 
  - 下一个CustomerOrderItemId 
选择coalesce（max（CustomerOrderItemId），0）+ 1 
从LineItem 
其中CustomerId = specific_customer_id 
和CustomerOrderId = specific_customer_order_id ; 
  

现在，如果我想查找属于某个客户可跳过 Order 表。

  select * 
 Customer as c 
将LineItem作为i连接到i.CustomerId = c.CustomerId 
其中CustomerID = 7; 
  

如果我不需要 Customer 表，不需要加入。比较这第一个例子 - 记住获得行项目是目标。

  select * 
从LineItem 
其中CustomerID = 7; 
  

因此，使用关系模型，通过传播（自然）键，

哪个更好？

希望你能够将基本原理转化为你的例子 - 我觉得很难使用泛型（A，B，C） 。

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?

解决方案

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

这篇关于多列外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！