title: Create unique constraint with null columns
body: <p>I have a table with this layout:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE Favorites (
    FavoriteId uuid NOT NULL PRIMARY KEY,
    UserId uuid NOT NULL,
    RecipeId uuid NOT NULL,
    MenuId uuid
  );
  </code></pre>
  <p>I want to create a unique constraint similar to this:</p>
  <pre><code>ALTER TABLE Favorites
  ADD CONSTRAINT Favorites_UniqueFavorite UNIQUE(UserId, MenuId, RecipeId);
  </code></pre>
  <p>However, this will allow multiple rows with the same <code>(UserId, RecipeId)</code>, if <code>MenuId IS NULL</code>. I want to allow <code>NULL</code> in <code>MenuId</code> to store a favorite that has no associated menu, but I only want at most one of these rows per user/recipe pair.</p>
  <p>The ideas I have so far are:</p>
  <ol>
  <li><p>Use some hard-coded UUID (such as all zeros) instead of null.<br />
  However, <code>MenuId</code> has a FK constraint on each user's menus, so I'd then have to create a special &quot;null&quot; menu for every user which is a hassle.</p>
  </li>
  <li><p>Check for existence of a null entry using a trigger instead.<br />
  I think this is a hassle and I like avoiding triggers wherever possible. Plus, I don't trust them to guarantee my data is never in a bad state.</p>
  </li>
  <li><p>Just forget about it and check for the previous existence of a null entry in the middle-ware or in a insert function, and don't have this constraint.</p>
  </li>
  </ol>
  <p>I'm using Postgres 9.0. Is there any method I'm overlooking?</p>