[sql] Foreign Key to non-primary key

I have a table which holds data, and one of those rows needs to exist in another table. So, I want a foreign key to maintain 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 table I foreign key to, the column isn't the PK. Is there a way to create this foreign key, or maybe a better way to maintain this referential integrity?

Primary keys always need to be unique, foreign keys need to allow non-unique values if the table is a one-to-many relationship. It is perfectly fine to use a foreign key as the primary key if the table is connected by a one-to-one relationship, not a one-to-many relationship.

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

Necromancing.
I assume when somebody lands here, he needs a foreign key to column in a table that contains non-unique keys.

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

You're for example keeping rooms in a table, with a room-uid primary key, a DateFrom and a DateTo field, and another uid, here RM_ApertureID to keep track of the same room, and a 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 as the same value as RM_UID. Then, when you terminate the room to a date, and re-establish it with a new date range, RM_UID is newid(), and the RM_ApertureID from the previous entry becomes the new RM_ApertureID.

So, if that's the case, RM_ApertureID is a non-unique field, and so you can't set a foreign-key in another table.

And there is no way to set a foreign key to a non-unique column/index, e.g. 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, data-garbage is the result sooner rather than later...

Now what you can do in this case (short of rewritting the entire application) is inserting a CHECK-constraint, with a scalar function checking the presence of 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

As others have pointed out, ideally, the foreign key would be created as a reference to a primary key (usually an IDENTITY column). However, we don't live in an ideal world, and sometimes even a "small" change to a schema can have significant ripple effects to the application logic.

Consider the case of a Customer table with a SSN column (and a dumb primary key), and a Claim table that also contains a SSN column (populated by business logic from the Customer data, but no FK exists). The design is flawed, but has been in use for several years, and three different applications have been built on the schema. It should be obvious that ripping out Claim.SSN and putting in a real PK-FK relationship would be ideal, but would also be a significant overhaul. On the other hand, putting a UNIQUE constraint on Customer.SSN, and adding a FK on Claim.SSN, could provide referential integrity, with little or no impact on the applications.

Don't get me wrong, I'm all for normalization, but sometimes pragmatism wins over idealism. If a mediocre design can be helped with a band-aid, surgery might be avoided.