Required vs Optional

Optionality: What Happens When the FK Is NULL? A required relationship means the FK cannot be NULL: every order MUST have a customer. An optional relationship means the FK can be NULL: an employee might not have a department yet. This distinction directly affects your queries. An INNER JOIN drops rows with NULL FKs. A LEFT JOIN preserves them but produces NULLs in the joined columns. In analytical models, the standard practice for optional FKs is to create an 'Unknown' member in the dimension table (e.g., customer_id = -1, name = 'Unknown'). Then the FK is never NULL. Every fact row joins successfully, and reports show 'Unknown' instead of silently dropping rows. The Unknown member is not a cosmetic convenience. It is a data integrity mechanism. Without it, an INNER JOIN to the dimension s