title: Creating a check constraint for values in another table with nulls
body: <p>I have two tables, A and B. The structure looks like this:</p>
  
  <pre><code>CREATE TABLE A (
      w int NOT NULL,
      x int NOT NULL,
      y int NOT NULL,
      CONSTRAINT PK_A PRIMARY KEY (w, x, y)
  )
  
  CREATE TABLE B (
      w int NOT NULL,
      y int NULL,
      z int NOT NULL
  )
  </code></pre>
  
  <p>I want to make sure for any set of values entered in table B, that w and y are in table A. If the y value in table B is null, I only want to make sure that w is in table A.</p>
  
  <p>Some sample data, inserts, and expected results:</p>
  
  <pre><code>Table A
  w  x  y
  ----------
  1  1  1
  1  1  2
  1  2  1
  1  3  2
  2  1  1
  
  INSERT INTO B (w, y, z) VALUES (1, 1, 3) -- good
  INSERT INTO B (w, y, z) VALUES (1, NULL, 3) -- good
  INSERT INTO B (w, y, z) VALUES (1, 1, 4) -- good
  INSERT INTO B (w, y, z) VALUES (2, NULL, 3) -- good
  INSERT INTO B (w, y, z) VALUES (1, 3, 1) -- fail
  INSERT INTO B (w, y, z) VALUES (3, NULL, 1) -- fail
  </code></pre>
  
  <p>Any way for this to work? I'm using SQL Server 2000 if that comes into play.</p>