var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Foreign Key constraint where Not Null
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.
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
Originally Posted by Wedgetail
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