Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    29

    Unanswered: Foreign Key constraint where Not Null

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Wedgetail View Post
    However, I need to apply the second constraint only if NOT(OfficeID IS NULL).
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •