title: Can a UNIQUE 'WHERE' index which allows multiple NULLS be used as the primary key for a foreign key relationship?
body: <p>Using SQL Server on Azure: 12.0.2000.8</p>
  
  <p>I've defined the following unique index for <code>TablePrimary</code>:</p>
  
  <pre><code>CREATE UNIQUE NONCLUSTERED INDEX [IX_TablePrimary_Id] 
  ON [dbo].[TablePrimary] ([PrimaryId] ASC)
  WHERE [PrimaryId] IS NOT NULL
  </code></pre>
  
  <p>The <code>WHERE</code> clause allows multiple rows to have <code>NULL</code> in this column, but every non-NULL value in this column must be unique.</p>
  
  <p>Should the indexed column in <code>TablePrimary</code> be suitable as the primary key for a foreign key relationship to <code>TableForeign</code>? Every non-NULL value must be unique and NULL in the foreign table wouldn't create a foreign key relationship anyway. But I am getting an error trying to place the foreign key relationship on <code>TableForeign</code>.</p>
  
  <blockquote>
    <p>Unable to create relationship 'FK_TableForeign_TablePrimary'.<br>
    There are no primary or candidate keys in the referenced table 'dbo.TablePrimary' that match the referencing column list in the foreign key 'FK_TableForeign_TablePrimary'.</p>
  </blockquote>
  
  <p>Before I dig deeper into this, I'd like to verify that what I am trying to do is indeed possible. Basically, not every row in <code>TablePrimary</code> will have children in <code>TableForeign</code>. But those rows which do exist in <code>TableForeign</code> must have a matching <code>PrimaryId</code> in <code>TablePrimary</code> (there are other ways to get the job done but in this case, I'd need a third table as the cross-ref between <code>TablePrimary</code> and <code>TableForeign</code> and I'd like to avoid that if it's not necessary, although, if it is necessary, then it is necessary).</p>