Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011
    Posts
    56

    Unanswered: Opinion: Table with Foreign Key that can be Null

    Access 2007
    SharePoint 2010

    I have an equipment database that is working relatively well right now except a small "problem" I have noticed. This is the current structure:

    tblEquipment - ID, Mfr, Model, SN, Type
    tblComponents - ID, EquipmentID, Mfr, Model, SN, Type
    tblFilters - ID, Mfr, Model, Type
    tblComponentFilters - ID, ComponentID, FilterID, Qty, Location

    I have noticed that sometimes a filter can be directly related to a component so I would have to make a "placeholder" component so that I can use its ID as the foreign key in tblComponentFilters. I was thinking of changing to a structure similar to this:

    tblEquipment - ID, Mfr, Model, SN, Type
    tblComponents - ID, EquipmentID, Mfr, Model, SN, Type
    tblFilters - ID, Mfr, Model, Type
    tblEquipmentFilters - ID, EquipmentID, ComponentID, FilterID, Qty, Location

    With this structure, tblEquipmentFilters.EquipmentID would be required while tblEquipmentFilters.ComponentID would be able to contain null values when there is no direct component to relate a filter to. What problems will occur because I am allowing ComponentID to contain nulls?

    I believe I may have to use Outer Join in some queries.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Nothing prevents you from having Null in the foreign key of a table. However you could have to change the type of the JOINs in queries.

    More explicitly:

    1. TableA
    PK_TableA
    FK_TableB
    Other columns...

    2. TableB
    PK_TableB
    Other Columns...

    With the following query, rows from TableA that don't have corresponding rows in TableB won't be in the rowset:
    Code:
    SELECT TableA.*, TableB.*
    FROM TableA
    INNER JOIN TableB ON TableA.FK_TableB = TableB.PK_TableB;
    While the same rows will be in the rowset with fields mapping columns from TableB set to Null:
    Code:
    SELECT TableA.*, TableB.*
    FROM TableA
    LEFT JOIN TableB ON TableA.FK_TableB = TableB.PK_TableB;
    Have a nice day!

Posting Permissions

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