title: Oracle - Partially nullable composite foreign key
body: <p>I have a legacy Oracle database that has a strange quirk I wanted to make sense of. It has a composite foreign key where <strong>some columns</strong> are nullable. To me this smells like a bad design by a careless developer, but I wanted to ask for opinions. Of course the original development team is long gone.</p>
  
  <p>The table is much larger in terms of columns but I think I was able to distill the issue in the example below:</p>
  
  <pre><code>create table quadrant (
    region number(9) not null,
    area number(9) not null,
    caption varchar2(20),
    primary key (region, area)
  );
  
  insert into quadrant (region, area, caption) values (10, 123, 'Chicago');
  insert into quadrant (region, area, caption) values (10, 125, 'Wisconsin');
  
  create table farm (
    id number(9),
    region_id number(9) not null,
    area_id number(9),
    name varchar2(50),
    constraint fk_region_area foreign key (region_id, area_id)
      references quadrant (region, area)
  );
  
  insert into farm (id, region_id, area_id, name) values (5, 10, null, 'farm 1');
  insert into farm (id, region_id, area_id, name) values (6, 11, null, 'farm 2');
  
  select * from farm;
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>ID  REGION_ID  AREA_ID  NAME
  --  ---------  -------  ------
  5   10         &lt;null&gt;   farm 1  &lt;-- Does it point to anything?
  6   11         &lt;null&gt;   farm 2  &lt;-- Region 11 doesn't even exist!
  </code></pre>
  
  <p>If parts of a foreign key are null, then what meaning does it have?</p>
  
  <ul>
  <li>Is <code>REGION_ID, AREA_ID = (10, null)</code> pointing to anything, or it's just useless info?</li>
  <li>Is <code>REGION_ID, AREA_ID = (11, null)</code> pointing to anything? I don't think so.</li>
  </ul>
  
  <p>I am tempted to add a constraint to enforce ALL or NONE in terms of null values on the foreign key. Does it make sense?</p>
  
  <p>But above all, what's the use case for this "feature"?</p>