title: Foreign key contraints in many-to-many relationships
body: <h2>Context</h2>
  
  <p>We're building a blog for an intro. to databases course project.</p>
  
  <p>In our blog, we want to be able to set <code>Labels</code> on <code>Posts</code>.  The <code>Labels</code> can't exist by themselves, they only do so if they are related to a <code>Posts</code>.  This way, <code>Labels</code> that are not used by any <code>Posts</code> shouldn't stay in the database.</p>
  
  <p>More than one <code>Label</code> can belong to a single <code>Post</code>, and more than a single <code>Post</code> can use a <code>Label</code>.</p>
  
  <p>We are using both SQLite3 (locally/testing) and PostgreSQL (deployment).</p>
  
  <h2>Implementation</h2>
  
  <p>Here is the SQL (SQLite3 flavor) that we use to create those two tables, along with the relationship table:</p>
  
  <h3>Posts</h3>
  
  <pre><code>CREATE TABLE IF NOT EXISTS Posts(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     authorId INTEGER,
     title VARCHAR(255),
     content TEXT,
     imageURL VARCHAR(255),
     date DATETIME,
     FOREIGN KEY (authorId) REFERENCES Authors(id) ON DELETE SET NULL
  )
  </code></pre>
  
  <h3>Labels</h3>
  
  <pre><code>CREATE TABLE IF NOT EXISTS Labels(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     name VARCHAR(255) UNIQUE,
     -- This is not working:
     FOREIGN KEY (id) REFERENCES LabelPosts(labelId) ON DELETE CASCADE 
  )
  </code></pre>
  
  <p><strong>LabelPosts</strong> (relation between <code>Post</code> [1..*] -- * <code>Label</code>)</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS LabelPosts(
      postId INTEGER,
      labelId INTEGER,
      PRIMARY KEY (postId, labelId),
      FOREIGN KEY (postId) REFERENCES Posts(id) ON DELETE CASCADE
  )
  </code></pre>
  
  <h2>Problem</h2>
  
  <ul>
  <li><p>Using SQLite3, <code>Labels</code> are not deleted from the database when I remove all references to it from the <code>LabelPosts</code> table.  I think for the reason given by Postgres, despite SQLite accepting the table without warning.</p></li>
  <li><p>PostgreSQL complains that <code>labelId</code> is not unique within <code>LabelPosts</code>, which is true and also required, since it's many-to-many:</p></li>
  </ul>
  
  <blockquote>
    <p>pq: S:"ERROR" R:"transformFkeyCheckAttrs" L:"6511" C:"42830" F:"tablecmds.c"<br>
    M:"there is no unique constraint matching given keys for referenced table \"labelposts\""</p>
  </blockquote>
  
  <p>So I understand that I'm doing my constraint wrong.  However I don't know how to do it properly.</p>