Foreign key to non-primary key

Question

I have a table that stores data, and one of these rows must exist in another table. Therefore, I want the foreign key to support referential integrity.

CREATE TABLE table1 ( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, AnotherID INT NOT NULL, SomeData VARCHAR(100) NOT NULL ) CREATE TABLE table2 ( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, AnotherID INT NOT NULL, MoreData VARCHAR(30) NOT NULL, CONSTRAINT fk_table2_table1 FOREIGN KEY (AnotherID) REFERENCES table1 (AnotherID) ) 

However, as you can see, the foreign key of table I, the column is not PK. Is there a way to create this foreign key, or perhaps a better way to maintain this referential integrity?

Answer 1

If you really want to create a foreign key for a non-primary key, it MUST be a column with a unique constraint.

From the online store :

A FOREIGN KEY constraint does not have to be associated only with a PRIMARY KEY constraint in another table; it can also be defined to reference UNIQUE constraint columns in another table.

So, in your case, if you make AnotherID unique, it will be resolved. If you cannot apply a unique constraint, you're out of luck, but it really makes sense if you think about it.

Although, as already mentioned, if you have a perfectly good primary key as a candidate key, why not use it?

Answer 2

As others have pointed out, ideally a foreign key will be created as a reference to the primary key (usually the IDENTITY column). However, we do not live in an ideal world, and sometimes even a “small” circuit change can have significant ripple effects for the application logic.

Consider the case of a Customer table with an SSN column (and a dumb primary key) and a requirements table that also contains an SSN column (populated with business logic from Customer data, but FK does not exist). The design is erroneous, but has been used for several years, and three different applications were built on the circuit. It should be obvious that tearing down the .SSN application and establishing a real PK-FK relationship would be ideal, but would also be a significant overhaul. On the other hand, including a UNIQUE constraint in Customer.SSN and adding FK to Claim.SSN can provide referential integrity with little impact on applications.

Do not get me wrong, I’m all for normalization, but sometimes pragmatism defeats idealism. If a mediocre design can help with a brace, surgery can be avoided.

Answer 3

Necromancing.

I assume that when someone lands here, he needs a foreign key for a column in a table containing non-unique keys.

The problem is that if you have this problem, the database schema is denormalized.

For example, you save rooms in a table with the primary key number-uid, the DateFrom and DateTo field and another uid, here RM_ApertureID, to track the same room and soft delete field, like RM_Status, where 99 means "deleted", and < > 99 means active.

So, when you create the first room, you insert RM_UID and RM_ApertureID into the same value as RM_UID. Then, when you close the room to a date and restore it with a new date range, RM_UID is newid (), and RM_ApertureID from the previous record becomes the new RM_ApertureID.

So, if so, RM_ApertureID is not a unique field, so you cannot set the foreign key in another table.

And there is no way to set a foreign key for a non-unique column / index, for example. in T_ZO_REM_AP_Raum_Reinigung (WHERE RM_UID is actually RM_ApertureID).

But to prohibit invalid values, you need to set a foreign key, otherwise the garbage data will be the result sooner rather than later ...

Now, what you can do in this case (without rewriting the entire application), inserts a CHECK constraint with a scalar function that checks for the key:

 IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]')) ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung DROP CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_Constaint_ValidRmApertureId]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[fu_Constaint_ValidRmApertureId] GO CREATE FUNCTION [dbo].[fu_Constaint_ValidRmApertureId]( @in_RM_ApertureID uniqueidentifier ,@in_DatumVon AS datetime ,@in_DatumBis AS datetime ,@in_Status AS integer ) RETURNS bit AS BEGIN DECLARE @bNoCheckForThisCustomer AS bit DECLARE @bIsInvalidValue AS bit SET @bNoCheckForThisCustomer = 'false' SET @bIsInvalidValue = 'false' IF @in_Status = 99 RETURN 'false' IF @in_DatumVon > @in_DatumBis BEGIN RETURN 'true' END IF @bNoCheckForThisCustomer = 'true' RETURN @bIsInvalidValue IF NOT EXISTS ( SELECT T_Raum.RM_UID ,T_Raum.RM_Status ,T_Raum.RM_DatumVon ,T_Raum.RM_DatumBis ,T_Raum.RM_ApertureID FROM T_Raum WHERE (1=1) AND T_Raum.RM_ApertureID = @in_RM_ApertureID AND @in_DatumVon >= T_Raum.RM_DatumVon AND @in_DatumBis <= T_Raum.RM_DatumBis AND T_Raum.RM_Status <> 99 ) SET @bIsInvalidValue = 'true' -- IF ! RETURN @bIsInvalidValue END GO IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]')) ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung DROP CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] GO -- ALTER TABLE dbo.T_AP_Kontakte WITH CHECK ADD CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung WITH NOCHECK ADD CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] CHECK ( NOT ( dbo.fu_Constaint_ValidRmApertureId(ZO_RMREM_RM_UID, ZO_RMREM_GueltigVon, ZO_RMREM_GueltigBis, ZO_RMREM_Status) = 1 ) ) GO IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]')) ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung CHECK CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] GO