如何使外键关系在“菱形”中保持一致,关系系统 [英] 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屋!