title: Can a linking table have two mutually exclusive columns?
body: <p>I have a problem where, due to previous people's poor design decisions, I'm forced to have two columns in a linking table that <em>should</em> always be mutually exclusive.  I've seen <a href="https://stackoverflow.com/questions/1832084/sql-server-define-columns-als-mutually-exclusive">SQL-Server: Define columns as mutually exclusive</a> but MySQL doesn't enforce CHECK constraints so I'm looking for other options.  Is there any way to enforce mutual exclusion between two columns?</p>
  
  <p>Let me explain in case someone sees a better solution.</p>
  
  <p>Previous dev/admins have constructed two tables that record basically the same type of things.  I have a new a table that is 1 -> many in theory to the thing they both represent.  So my two options seem to be to create either two bridging tables or 1 bridging tables with two external key references.  The two tables seems like an ugly solution to me that I was hoping to avoid but unenforced mutually exclusive fields leaves me open to easy corruption.</p>
  
  <p>Concrete example since I can't share my real case:</p>
  
  <pre><code>Table 'full_time_students' stores full time students
  Table 'part_time_students' store part time students
  
  Table 'class' will store class info
  Table 'class_students' should store which students are in which class
  </code></pre>
  
  <p>In an ideal world, I would combine the two student tables but there's a lot of code that would break and I don't have the time/will to go fix all of that so I'm forced for now to deal with the existent schemas/data.  I'm using InnoDB so I was already planning on having foreign key constraints on the student_ids but like I said, if I do one table with two columns I open myself up to the corruption of having one row with both ids.</p>
  
  <p>Just writing this out almost convinces me to reconsider the 2 table solution...</p>