title: MySQL InnoDB - Constraints for keys linking parent to one of two children
body: <p>Using MySQL, moving from MyISAM to InnoDB tables. Database design started with dumping the data and re-importing it without foreign keys or constraints.  Adding those one at a time to find errors.</p>
  
  <p>I have <code>ParentTable</code> which can either be linked to <code>ChildTableA</code> or <code>ChildTableB</code>, but not both.  Should the <code>CREATE</code> syntax be: (using <code>CREATE</code> syntax for simplicity rather than multiple <code>ALTER</code>s)</p>
  
  <pre><code>CREATE TABLE `ParentTable`
     `IDParentTable` bigint(20) unsigned NOT NULL auto_increment,
     `IDChildTableA` bigint(20) unsigned NOT NULL default '0',
     `IDChildTableB` bigint(20) unsigned NOT NULL default '0',
     PRIMARY KEY (`IDParentTable`),
     KEY `ParentTable_IDChildTableA` (`IDChildTableA`),
     KEY `ParentTable_IDChildTableB` (`IDChildTableB`)
  ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>Without thinking about it, I tried including:</p>
  
  <pre><code>CONSTRAINT `ParentTable_IDChildTableA` FOREIGN KEY (`IDChildTableA`) REFERENCES `ChildTableA` (`IDChildTableA`),
  CONSTRAINT `ParentTable_IDChildTableB` FOREIGN KEY (`IDChildTableB`) REFERENCES `ChildTableB` (`IDChildTableB`)
  </code></pre>
  
  <p>Which failed, because many rows have 0 for <code>IDChildTableA</code>, and many rows have 0 for <code>IDChildTableB</code>.  But, no rows have 0 for both.  It's seeing that no <code>ChildTableA</code> exists with <code>IDChildTableA</code> of 0, and likewise with B.</p>
  
  <p>Is there a proper way to handle this situation while keeping referential integrity?  Without splitting <code>ParentTable</code> in two?  A way to say it's OK if it's 0 or references a valid related table? Or, does wanting polymorphic tables mean I have to go without constraints?
  BTW, I much prefer this route than having a single <code>IDChildTable</code> foreign key and then having another column designating whether it's table A or B... Not how I see that would work either for constraints, just saying I prefer not to go that route...</p>