title: postgres many to one unique constraint
body: <p>I'm curious if there is a way to write a unique constraint to support the following situation.</p>
  <p>Suppose I have table <code>table1</code> with facts about a user, with four columns:</p>
  <ul>
  <li><code>user_id</code>: unique id for user</li>
  <li><code>source</code>: where the detail came from</li>
  <li><code>d1</code>: dimension 1 of the fact</li>
  <li><code>d2</code>: dimension 2 of the fact</li>
  </ul>
  <p>The following is an example of data in this table:</p>
  <pre><code>| row_id | user_id | source | d1     | d2      |
  |--------|---------|--------|--------|---------|
  | 1      | aaa111  | foo    | bar    | 123     |
  | 2      | aaa111  | foo    | baz    | 'horse' |
  | 3      | aaa111  | scrog  | bar    | 123     |
  | 4      | bbb222  | foo    | goober | 456     |
  </code></pre>
  <p>Currently, a unique constraint exists on <code>source + d1 + d2</code>.  This is good, because it allows the <strong>same user</strong> to have duplicates of <code>(d1,d2)</code>, as long as they have a different <code>source</code>.
  Rows #1 and #3 demonstrate this for user <code>aaa111</code>.</p>
  <p>However, this constraint does not prevent the following row from getting added...</p>
  <pre><code>| row_id | user_id | source | d1     | d2      |
  |--------|---------|--------|--------|---------|
  | 1      | aaa111  | foo    | bar    | 123     |
  | 2      | aaa111  | foo    | baz    | 'horse' |
  | 3      | aaa111  | scrog  | bar    | 123     |
  | 4      | bbb222  | foo    | goober | 456     |
  | 5      | bbb222  | turnip | baz    | 'horse' |  &lt;---- allowed new row
  </code></pre>
  <p>...because <code>source</code> is different for rows #2 and #5.</p>
  <p>I would like to add a unique constraint where the combination of <code>(d1,d2)</code> may only exist for a <strong>single</strong> <code>user_id</code>.</p>
  <p>Said another way, a single user can have as many unique <code>(source, d1, d2)</code> combinations as needed, but cannot share <code>(d1,d2)</code> with another <code>user_id</code>.</p>
  <p>Is this data model fundamentally flawed to support this constraint?  or is there a unique constraint that might help enforce this?  Thanks in advance for any suggestions.</p>