title: Multiple-reference foreign keys in table definition?
body: <h2>Summary</h2>
  
  <p>How do I make it easy for non-programmers to write a query such as the following?</p>
  
  <pre><code>select
      table_name.*
    , foreign_table_1.name
    , foreign_table_2.name
  from
    table_name
  left outer join foreign_table foreign_table_1 on foreign_table_1.id = foreign_1_id
  left outer join foreign_table foreign_table_2 on foreign_table_2.id = foreign_1_id
  ;
  </code></pre>
  
  <h2>Context</h2>
  
  <p>I have a situation like this:</p>
  
  <pre><code>create table table_name (
      id integer primary key autoincrement not null
    , foreign_key_1_id integer not null
    , foreign_key_2_id integer not null
    , some_other_column varchar(255) null
  );
  
  create table foreign_table (
      id integer primary key autoincrement not null
    , name varchar(255) null
  );
  </code></pre>
  
  <p>...in which both <code>foreign_key_1_id</code> and <code>foreign_key_2_id</code> reference <code>foreign_table</code>.  (Obviously, this is simplified and abstracted.)  To query and get the respective values, I might do something like this:</p>
  
  <pre><code>select
      table_name.*
    , foreign_table_1.name
    , foreign_table_2.name
  from
    table_name
  left outer join foreign_table foreign_table_1 on foreign_table_1.id = foreign_1_id
  left outer join foreign_table foreign_table_2 on foreign_table_2.id = foreign_1_id
  ;
  </code></pre>
  
  <p>(That is, alias <code>foreign_table</code> in the join to link things up correctly.)  This works fine.  However, some of my clients want to use <a href="http://www.sqlmaestro.com/" rel="nofollow noreferrer" title="SQL Maestro">SQL Maestro</a> to query the tables.  This program uses the foreign key information to link up tables using a fairly straightforward interface ("Visual Query Builder").  For instance, the user can pick multiple tables and SQL Maestro will fill in the joins, like seen here:</p>
  
  <p><a href="https://i.stack.imgur.com/IMila.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IMila.png" alt="Visual Query Builder"></a><br>
  <sub>(source: <a href="http://sqlmaestro.com/data/912/1183273856-250x238.png" rel="nofollow noreferrer">sqlmaestro.com</a>)</sub>  </p>
  
  <p>(That's a diagram from their website, just for illustration.)</p>
  
  <p>This strategy works great as long as foreign keys only reference one table.  The multiple-reference situation seems to be confusing it because it gneerates SQL like this:</p>
  
  <pre><code>SELECT 
    table_name.some_other_column,
    foreign_table.name
  FROM
    table_name
    INNER JOIN foreign_table ON (table_name.foreign_key_1_id = foreign_table.id)
    AND (table_name.foreign_key_2_id = foreign_table.id)
  </code></pre>
  
  <p>...because the foreign keys are defined as follows:</p>
  
  <pre><code>create table table_name (
      id integer primary key autoincrement not null
    , foreign_key_1_id integer not null
    , foreign_key_2_id integer not null
    , some_other_column varchar(255) null
  
    ---------------------------
    -- The part that changed:
    ---------------------------
    , foreign key (foreign_key_1_id) references foreign_table(id)
    , foreign key (foreign_key_2_id) references foreign_table(id)
  );
  
  create table foreign_table (
      id integer primary key autoincrement not null
    , name varchar(255) not null
  );
  </code></pre>
  
  <p>That's a problem because you only get 1 <code>foreign_table.name</code> value back, whereas there are often 2 separate values.</p>
  
  <h2>Question</h2>
  
  <p>How would I go about defining foreign keys to handle this situation?  (Is it possible or does it make sense to do so?  I wouldn't think that it would make a big difference in constraint checking, so I've thought that that is a reason I can't find any information.)  My end goal is to make querying this information easy for my clients to do by themselves, and although this situation doesn't happen every day, it's time-consuming / frustrating to have to help people through it every time it comes up.</p>
  
  <p>If there isn't a way of solving my foreign key problem this way, can you suggest any alternatives?  I already have some ways of getting people this information though views, but people often need to have more flexibility than that.</p>