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?]

I'm starting to think that the uniqueness of a primary key probably implies that the differences, e.g. in performance, between a foreign key that references a primary key versus one that references a unique key are minimal. — Kenny Evitt
If the office codes are subject to change, you'd be well advised to whack ON UPDATE CASCADE on the foreign key constraint. — Damien_The_Unbeliever
@Damine_The_Unbeliever – that is good advice, but it's not related to my question. — Kenny Evitt

gbn gbn · Accepted Answer · 2011-03-20T07:51:22

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.

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

6 Answers