title: Constrain a column across tables
body: <p>I'm using Postgres to store formulae and elements of formulae across two tables. Basically, you have something like:</p>
  <h2>Elements Table</h2>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>symbol</th>
  <th>content</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>pi</td>
  <td>3.1.45</td>
  </tr>
  <tr>
  <td>lune</td>
  <td>42</td>
  </tr>
  </tbody>
  </table>
  </div><h2>Formula Table</h2>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>symbol</th>
  <th>content</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>area</td>
  <td>pi*r^2</td>
  </tr>
  <tr>
  <td>rugsize</td>
  <td>area*lune</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>So, formulae can use elements but also other formulae in their content field. For this reason (and for general reduction of confusion) I would like to make symbol unique across both tables.</p>
  <p>I can, of course, in the code that's doing the insertion, look up the entries before adding them and refuse to add a duplicate symbol. (I probably will do this, but I don't want the database reliant on that.) I could also require a tag within the formula table to specify when it's using another formula:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>symbol</th>
  <th>content</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>rugsize</td>
  <td>f(area)*lune</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I'm not crazy about that since it puts a burden on the user to remember that, or on the coder to secretly add and remove the &quot;f()&quot;.</p>
  <p>Everything I found on Stack and elsewhere went the other way: Forcing a column value to be present in another table, except for one suggestion that the unique items be kept in a separate table.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>symbol</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>area</td>
  </tr>
  <tr>
  <td>lune</td>
  </tr>
  <tr>
  <td>pi</td>
  </tr>
  <tr>
  <td>rugsize</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>And then...actually, I'm still not sure how that would work at the DB level.</p>
  <p>So, is there a way to do this with constraints or foreign keys, or must I write a trigger for each table to look into the other table?</p>
  <p>Addition: I've simplified here greatly but the elements table is much more complex than I'm showing and has little in common with the formulae table.</p>
  <p>Edited to add the above addition and to try to fix the one-column &quot;symbol&quot; table which looks fine in the editing preview but does not format correctly on the actual page.</p>