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

问题描述

请考虑这种情况：汽车是从销售人员购买的。销售人员在展厅（且只能在一个展厅）工作。展厅附属于制造商，且只销售该制造商生产的汽车。同时，汽车是特定的型号，而型号是由制造商制造的。

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：

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

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.

>

以下是键的一些详细信息

Here are few details for keys

--
-- 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屋！