title: implementing UNIQUE across linked tables in MySQL
body: <p>a USER is a PERSON and a PERSON has a COMPANY - user -> person is one-to-one, person -> company is many-to-one.</p>
  
  <p>person_id is FK in USER table.
  company_id is FK in PERSON table.</p>
  
  <p>A PERSON may not be a USER, but a USER is always a PERSON.</p>
  
  <p>If company_id was in user table, I could create a unique key based on username and company_id, but it isn't, and would be a duplication of data if it was.</p>
  
  <p>Currently, I'm implementing the unique username/company ID rule in the RoseDB manager wrapper code, but it feels wrong. I'd like to define the unique rule at the DB level if I can, but I'm not sure excactly how to approach it. I tried something like this:</p>
  
  <pre><code>alter table user add unique(used_id,person.company_id);
  </code></pre>
  
  <p>but that doesn't work.</p>
  
  <p>By reading through the documentation, I can't find an example that does anything even remotely similar. Am I trying to add functionality that doesn't exist, or am I missing something here?</p>