title: What are the drawbacks of foreign key constraints that reference non-primary-key columns?
body: <p>I want to know if there are any drawbacks between a referential relation that uses primary key columns versus unique key columns (in SQL Server a foreign key constraint can only reference columns in a primary key or unique index).</p>
  
  <p>Are there differences in how queries are parsed, in specific DB systems (e.g. Microsoft SQL Server 2005), based on whether a foreign key references a primary key versus a unique key?</p>
  
  <p>Note that I'm <em>not</em> asking about the differences between using columns of different datatypes for referential integrity, joins, etc.</p>
  
  <p>Purely as an example, imagine a DB in which there is a 'lookup table' <code>dbo.Offices</code>:</p>
  
  <pre><code>CREATE TABLE dbo.Offices (
      ID   int NOT NULL IDENTITY(1,1) CONSTRAINT PK_Codes PRIMARY KEY,
      Code varchar(50) NOT NULL CONSTRAINT UQ_Codes_Code UNIQUE
  );
  </code></pre>
  
  <p>There is also a table <code>dbo.Patients</code>:</p>
  
  <pre><code>CREATE TABLE dbo.Patients (
      ID         int NOT NULL IDENTITY(1,1) CONSTRAINT PK_Patients PRIMARY KEY,
      OfficeCode varchar(50) NOT NULL,
      ...
      CONSTRAINT FK_Patients_Offices FOREIGN KEY ( OfficeCode )
          REFERENCES dbo.Offices ( Code )
  );
  </code></pre>
  
  <p>What are the drawbacks of the table <code>dbo.Patients</code> and its constraint <code>FK_Patients_Offices</code> as in the T-SQL code above, versus the following alternate version:</p>
  
  <pre><code>CREATE TABLE dbo.Patients (
      ID       int NOT NULL IDENTITY(1,1) CONSTRAINT PK_Patients PRIMARY KEY,
      OfficeID int NOT NULL,
      ...
      CONSTRAINT FK_Patients_Offices FOREIGN KEY ( OfficeID )
          REFERENCES dbo.Offices ( ID )
  );
  </code></pre>
  
  <p>Obviously, for the second version of <code>dbo.Patients</code>, the values in the column <code>OfficeID</code> don't need to be updated if changes are made to values in the <code>Code</code> column of <code>dbo.Offices</code>.</p>
  
  <p>Also (obvious) is that using the <code>Code</code> column of <code>dbo.Offices</code> for foreign key references largely defeats the purpose of the surrogate key column <code>ID</code> – this is purely an artifact of the example. [Is there a better example of a table for which foreign key references might <em>reasonably</em> use a non-primary key?]</p>