Hi Forum,

I am relatively new to SQL and am trying to write a Table Constraint [Foreign Key], but have it only apply when one column is not NULL.

Here are the tables.

Contact
ContactID
PersonID
CompanyID

ContactOffice
ContactID
OfficeID
...

PhoneNumber
PhoneID
ContactID
OfficeID
PhoneNumber

The use case is:
A contact may or may not be assigned to an office.

As such, a Phone Number may be assigned to just the Contact [ie. No entry in the ContactOffice table], or to the Contact at the Office [ie. Entry in ContactOffice table].

I was looking to assign two Foreign Key constraints to the PhoneNumber table.

CONSTRAINT FOREIGN KEY ("ContactID") REFERENCES "Contact"("ContactID")

CONSTRAINT FOREIGN KEY ("ContactID","OfficeID") REFERENCES "ContactOffice"("ContactID","OfficeID")

However, I need to apply the second constraint only if NOT(OfficeID IS NULL). Am I after a CHECK constraint which references the ContactOffice table?

Could someone please help me with this constraint.

you're fine the way it is

just ensure that the PK of ContactOffice is (ContactID,OfficeID)

since PKs must be not null, therefore (ContactID,OfficeID) in PhoneNumber can only reference ContactOffice when both columns are not null

if OfficeID is null, then the FK to ContactOffice doesn't apply