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'
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's additionally a table
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 ) );
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 ( 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 ) );
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
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
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.