Clever Geek Handbook

SQL: Check constraint depends on other tables

I have 3 tables: member, employer and location.

Member has MemberID, EmployerID and LocationID.
The employer has an EmployerID.
Location has EmployerID, LocationID.

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

I need to make a check constraint on a member that says

A member's seat is null or a seat owned by his employer

How do I restrict Member.LocationID to a location that has the same EmployerID? ie Member.EmployerID = Location.EmployerID

:?

+3


source to share


1 answer


You can create a unique index / primary key on Location (EmployerID, LocationID)

(I suppose this may already be the case)

Then specify multiple FK columns referencing 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)
)
+1


source






More articles:


All Articles