I wish to determine if you will find any disadvantages from a referential relation that utilizes primary key posts versus unique key posts (in SQL Server an overseas key constraint are only able to reference posts inside a primary key or unique index).

Exist variations in how queries are parsed, in specific DB systems (e.g. Microsoft SQL Server 2005), according to whether an overseas key references a principal key versus a distinctive key?

Observe that I am not asking concerning the variations between using posts of various datatypes for referential integrity, joins, etc.

Purely for example, make a DB by which there's a 'lookup table' dbo.Offices:

CREATE TABLE dbo.Offices (
    Code varchar(50) NOT NULL CONSTRAINT UQ_Codes_Code UNIQUE

There's additionally a table dbo.Patients:

CREATE TABLE dbo.Patients (
    OfficeCode varchar(50) NOT NULL,
    CONSTRAINT FK_Patients_Offices FOREIGN KEY ( OfficeCode )
        REFERENCES dbo.Offices ( Code )

Do you know the disadvantages on the table dbo.Patients and it is constraint FK_Patients_Offices as with the T-SQL code above, versus the next alternate version:

CREATE TABLE dbo.Patients (
    OfficeID int NOT NULL,
    CONSTRAINT FK_Patients_Offices FOREIGN KEY ( OfficeID )
        REFERENCES dbo.Offices ( ID )

Clearly, for that second version of dbo.Patients, the values within the column OfficeID don't have to be up-to-date if changes are created to values within the Code column of dbo.Offices.

Also (apparent) is the fact that while using Code column of dbo.Offices for foreign key references largely defeats the objective of the surrogate key column ID – this really is purely an artifact from the example. [It is possible to better illustration of a table that foreign key references might reasonably make use of a non-primary key?]

Why do you consider there'd be any disadvantages??

Quite the contrary! It is good to determine you are enforcing referential integrity as everybody should! No disadvantages - just sound practice to get this done!

I do not use whatever functional difference or any problems/difficulties with referencing a distinctive index versus. referencing a principal key.

Update: since you are not thinking about performance- or datatype-related issues, this last paragraph most likely does not add any extra value.

The only real minor factor I see is your OfficeCode is both a VARCHAR and therefore you may encounter difficulties with collation and/or casing (upper-/lower-situation, based on your collation), and JOIN's on a pretty big (as much as 50 bytes) and different length area are most likely less than as efficient as JOIN conditions with different small, fixed-length INT column.

There's no drawback.


Why have you got an ID column within the Offices table? A surrogate secret is accustomed to reduce space and improve performance over, say, a varchar column when utilized in other tables like a foreign key.

If you are planning to make use of the varchar column for foreign secrets, then you do not need a surrogate key.

Most advantages of getting the IDENTITY are thrown away using the Code column for FKs.

A principal secret is an applicant key and isn't essentially not the same as every other candidate key. It's a broadly observed convention that certain candidate key per table is designated like a "primary" one which this is actually the key employed for all foreign key references.

A potential benefit of singling out one type in by doing this is you make using the important thing clearer to customers from the database: they are fully aware which secret is the main one being recommended without searching in each and every referencing table. This really is entirely optional however. If you discover it easy to do otherwise or maybe needs dictate that another key ought to be recommended with a foreign key then It is best to do this.