sql - SQL:检查约束取决于其他表

Question

我有3个表:“成员”，“雇主”和“位置”。

成员具有MemberID，EmployerID和LocationID。
雇主具有EmployerID。
位置具有EmployerID，LocationID。

Member <<---> Employer
Location <<---> Employer
Member <---> Location

我需要对成员说一个检查约束

成员的位置为空或属于其雇主的位置

如何将Member.LocationID约束到具有相同EmployerID的位置？即: Member.EmployerID = Location.EmployerID？

Answer 1

您可以在Location (EmployerID, LocationID)上创建唯一的索引/主键(我认为可能已经是这种情况)

然后有一个多列FK从Member引用它

CREATE TABLE Location
(
EmployerID INT,
LocationID INT,
PRIMARY KEY (EmployerID,LocationID)
)


CREATE TABLE Member
(
MemberID INT PRIMARY KEY,
EmployerID INT,
LocationID INT,
FOREIGN KEY (EmployerID,LocationID)
    REFERENCES Location (EmployerID,LocationID)
)