如何提供约束到一个关联表有两个源表与一个公共的外键？ [英] How to provide constraint to an associative table relating to two source tables with a common foreign key?

问题描述

示例场景。

在航班调度系统中，有一个 pilot code> plane_type 表指示飞行员能够飞行的飞机（假设是多对一关系）。

In a flight schedule system, there are a pilot table which refers to a plane_type table indicating the planes the pilot is able to fly (assuming that is a many-to-one relation).

还有一个平面表，它指的是表示飞机类型的 plane_type 表
也是多对一关系）。

There are also a plane table and it refers to the plane_type table to indicate the plane's type (also many-to-one relation).

现在有一个关联表 flight_plan $ c>飞行员飞往飞机。

Now there is an associative table flight_plan which assigns a pilot to a plane for a given flight.

如何确保 pilot 的资格符合 plane 此航班的类型？

How do I make sure the pilot's qualification do match the plane's type for this flight?

在数据库设计中实现这个约束的任何可能性？非常感谢。

Any possibility to implement this as a constraint in a database design? Thank you.

已编辑：

请参阅下图，确保 pilot.plane_type 等于 plane.plane_type ？

Refering to the diagram below, how to make sure pilot.plane_type is equal to plane.plane_type?

推荐答案

Plane 在上有唯一索引（AK）PlaneID，PlaneTypeID

>

编辑

create table Pilot (PilotID integer);
alter table Pilot add constraint PK_Pilot primary key (PilotID);

create table PlaneType (PlaneTypeID integer);
alter table PlaneType add constraint PK_PlaneType primary key (PlaneTypeID);

create table PilotQualification (PilotID integer, PlaneTypeID integer);
alter table PilotQualification 
  add constraint  PK_PilotQual primary key (PilotID, PlaneTypeID)
, add constraint FK1_PilotQual foreign key (PilotID)     references Pilot(PilotID)
, add constraint FK2_PilotQual foreign key (PlaneTypeID) references PlaneType(PlaneTypeID) ;

create table Plane (PlaneID integer, PlaneTypeID integer);
alter table Plane
  add constraint  PK_Plane primary key (PlaneID)
, add constraint FK1_Plane foreign key (PlaneTypeID) references PlaneType(PlaneTypeID) ;
create unique index AK_Plane on Plane (PlaneID, PlaneTypeID) ;

create table PlanePilot (PlaneID integer, PlaneTypeID integer, PilotID integer) ;
alter table PlanePilot
  add constraint  PK_PlanePilot primary key (PlaneID, PlaneTypeID, PilotID)
, add constraint FK1_PlanePilot foreign key (PilotID, PlaneTypeID) references PilotQualification(PilotID, PlaneTypeID)
, add constraint FK2_PlanePilot foreign key (PlaneID, PlaneTypeID) references Plane(PlaneID, PlaneTypeID)
, add constraint FK3_PlanePilot foreign key (PilotID) references Pilot(PilotID) ;

这篇关于如何提供约束到一个关联表有两个源表与一个公共的外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！