如何在“菱形"中保持外键关系一致关系系统 [英] How to keep foreign key relations consistent in a "diamond-shaped" system of relationships

问题描述

考虑这种情况:汽车是从销售人员处购买的.一名销售人员在陈列室(并且仅在一个陈列室)工作.陈列室隶属于制造商，仅销售该制造商制造的汽车.同时，汽车是特定的Model，而Model是由制造商制造的.

Consider this situation: a Car is bought from a Salesperson. A Salesperson works at a Showroom (and at only one Showroom). A Showroom is affiliated to a Manufacturer, and only sells cars made by that Manufacturer. At the same time, a Car is of a particular Model, and a Model is made by a Manufacturer.

限制 R:汽车模型的制造商必须与汽车销售员的陈列室的附属制造商是同一制造商.

Restriction R: A Car's Model's Manufacturer must be the same Manufacturer as the Car's Salesperson's Showroom's affiliated Manufacturer.

该图显示了明显的外键关系.

The diagram shows the obvious foreign key relationships.

     ---->  Manufacturer  <----
     |                        |
     |                        |
 Showroom                     |
     ^                        |
     |                      Model
     |                        ^
Salesperson                   |
     ^                        |
     |                        |
     ---------  Car  ----------

您如何执行限制 R?您可以添加外键关系 Car -->制造商.然而，汽车制造商可以通过围绕钻石"以一种或另一种方式连接表格来建立，所以这样做肯定不会被规范化吗?但我不知道如何强制执行约束.

How do you enforce Restriction R? You could add a foreign key relationship Car --> Manufacturer. Yet the Manufacturer of a Car can be established by joining tables one way or another around the "diamond", so surely to do this would not be normalised? And yet I do not know otherwise how to enforce the constraint.

推荐答案

如果我理解正确，这应该很接近了.

If I understood the question correctly, this should be close.

这里有一些键的细节

--
-- Keys for SalesPerson
--
alter table SalesPerson
  add constraint PK_salesperson primary key (PersonID)

, add constraint AK1_salesperson unique (ManufacturerID, ShowRoomNo, PersonID) 

, add constraint FK1_salesperson foreign key (PersonID)
                           references Person (PersonID)

, add constraint FK2_salesperson foreign key (ManufacturerID, ShowRoomNo)
                         references ShowRoom (ManufacturerID, ShowRoomNo)
;

--
-- keys for Sale table
--
alter table Sale
  add constraint PK_sale primary key (SaleID)

, add constraint FK1_sale foreign key (BuyerID)
                    references Person (PersonID)

, add constraint FK2_sale foreign key (ManufacturerID, ModelName, ShowRoomNo)
                references CarDisplay (ManufacturerID, ModelName, ShowRoomNo)

, add constraint FK3_sale foreign key (ManufacturerID, ShowRoomNo, SalesPersonID)
               references SalesPerson (ManufacturerID, ShowRoomNo, PersonID)
;

这篇关于如何在“菱形"中保持外键关系一致关系系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！