SQL: Check constraint depends on other tables

Question

I have 3 tables: Member, Employer, and Location.

Member has MemberID, EmployerID, and LocationID.
Employer has EmployerID.
Location has EmployerID, LocationID.

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

I need to make a check constraint on member that says

A member's location is either null, or a location belonging to its employer

How do I constrain Member.LocationID to a location having the same EmployerID? ie: Member.EmployerID = Location.EmployerID?

Answer 1

You can create a unique index/primary key on Location (EmployerID, LocationID) (I take it that this may well be the case already)

Then have a multi column FK referencing that from 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)
)