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

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开发者_JS百科 example of a table for which foreign key references might reasonably use a non-primary key?]

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.

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.

A primary key is a candidate key and is not fundamentally different from any other candidate key. It is a widely observed convention that one candidate key per table is designated as a "primary" one and that this is the key used for all foreign key references.

A possible advantage of singling out one key in this way is that you make the use of the key clearer to users of the database: they know which key is the one being referenced without looking in every referencing table. This is entirely optional however. If you find it convenient to do otherwise or if requirements dictate that some other key should be referenced by a foreign key then I suggest you do that.

Assuming you add an index on the code column (which you definitely should as soon as you reference to it), is there anything to be said against getting rid of the entire ID column and using the code column as PK as well?

The most significant one I can think of is that, if they ever renumber the offices, you'll either lose integrity or need to update both tables. However likely that might be.

The performance consequences are vanishingly small unless you have irrationally large office codes, and even then less than you probably expect.

It's not considered a significant determinant of database design for most people.

Big flaw We were able to enter some value into dbo.Patients.OfficeID that is not there in dbo.Offices.ID There is no meaning to say that there is a reference.