What are the drawbacks of foreign key constraints that reference non-primary-key columns?

Question

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).

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?

Note that I'm not asking about the differences between using columns of different datatypes for referential integrity, joins, etc.

Purely as an example, imagine a DB in which there is a 'lookup table' dbo.Offices:

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
);

There is also a table dbo.Patients:

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 )
);

What are the drawbacks of the table dbo.Patients and its constraint FK_Patients_Offices as in the T-SQL code above, versus the following alternate version:

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 )
);

Obviously, for the second version of dbo.Patients, the values in the column OfficeID don't need to be updated if changes are made to values in the Code column of dbo.Offices.

Also (obvious) is that using the Code column of dbo.Offices for foreign key references largely defeats the purpose of the surrogate key column ID – this is purely an artifact of the example. [Is there a better example of a table for which foreign key references might reasonably use a non-primary key?]

Answer 1

There is no drawback.

However..

Why do you have an ID column in the Offices table? A surrogate key is used to reduce space and improve performance over, say, a varchar column when used in other tables as a foreign key.

If you are going to use the varchar column for foreign keys, then you don't need a surrogate key.

Most benefits of having the IDENTITY are squandered by using the Code column for FKs.

Answer 2

Why do you think there would be any drawbacks??

Quite the contrary! It's good to see you're enforcing referential integrity as everyone should! No drawbacks - just good practice to do this!

I don't see any functional difference or any problems/issues with referencing a unique index vs. referencing a primary key.

---

Update: since you're not interested in performance- or datatype-related issues, this last paragraph probably doesn't add any additional value.

The only minor thing I see is that your OfficeCode is both a VARCHAR and thus you might run into issues with collation and/or casing (upper-/lower-case, depending on your collation), and JOIN's on a fairly large (up to 50 bytes) and varying length field are probably not quite as efficient as JOIN conditions based on a small, fixed-length INT column.