title: SQL Server, self-referential data, how do I add a constraint for this
body: <p>Imagine I have the following structure:</p>
  
  <pre><code>DECLARE @Products TABLE (
      MemberId INT,
      ProductId INT,
      GlobalProductId INT,
      PRIMARY KEY (MemberId, ProductId));
  INSERT INTO @Products VALUES (1, 1, NULL);--this is my "global product"
  INSERT INTO @Products VALUES (2, 1, NULL);--this is okay
  INSERT INTO @Products VALUES (2, 2, 1);--this is okay
  INSERT INTO @Products VALUES (2, 3, 2);--this should fail
  SELECT * FROM @Products;
  </code></pre>
  
  <p>The rule I want to enforce is that MemberId = 1 holds global products and all other MemberIds hold normal products.  A set of normal products can be linked to a single global product.</p>
  
  <p>So I want the ability for a Member's Product to be linked to a Global Product, i.e. there would be a foreign key constraint that if the GlobalProductId isn't NULL then there should exist a ProductId that matches the GlobalProductId where the MemberId = 1.</p>
  
  <p>In my example above I have one global product with a ProductId = 1.  Then I create three normal products:</p>
  
  <ul>
  <li>the first has no global product;</li>
  <li>the second is linked to the single global product I created earlier (then I could link further products to the same global product);</li>
  <li><p>the third should fail as I have linked it to a global product that doesn't exist, i.e. this script will return nothing:</p>
  
  <pre><code>SELECT * FROM @Products WHERE MemberId = 1 AND ProductId = 2;
  </code></pre></li>
  </ul>
  
  <p>I can see that the simplest solution would be to create a new table to hold nothing but Global Products.  The problem with this approach is that I have a whole set of routines to load, update, delete data from the Product table and a second set of routines to perform calculations, etc. from the same table.  If I were to introduce a new "Global Products" table then I would have to duplicate dozens of UDFs to achieve this and my code would become much more complicated.</p>