Design encouraging FK to non-unique/non-PK

My current design is makes me want to create a FK to a non-unique/non-PK. Is this a code smell and/or does my overall design make sense?

CREATE TABLE [User].[ConnectionRequest]
(
    [ConnectionRequestId] INT NOT NULL PRIMARY KEY IDENTITY,
    [Requestor] INT NOT NULL, -- FK to internal user table
    [Target] INT NOT NULL, -- FK to ConnectionRequestTarget table
    [Status] INT NOT NULL, -- FK to status enum descriptions
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),

    CONSTRAINT [FK_Name_Truncated_For_Space] 
      FOREIGN KEY ([Target]) 
      REFERENCES [User].[ConnectionRequestTarget]([ConnectionRequestTargetId]),     
    CONSTRAINT [Unique_ConnectionRequest_Target_Status] UNIQUE ([Target], [Status])
    -- Other FK's removed for brevity
)

CREATE TABLE [User].[ConnectionRequestTarget]
(
    [ConnectionRequestTargetId] INT NOT NULL PRIMARY KEY IDENTITY,
    [InternalUserId] INT NULL, -- FK to internal user table, can be updated
    [ReferralId] INT NULL, -- FK to referral table
    [RequestCreated] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    -- FK's removed for brevity
);

CREATE TABLE [User].[PushSent]
(
    [PushSentId] INT NOT NULL PRIMARY KEY IDENTITY,
    [MatchingToken] UNIQUEIDENTIFIER NOT NULL,
    [PushDeviceId] INT NOT NULL,
    [Category] INT NOT NULL,
    [MonitoringId] INT NULL,
    [ConnectionRequestTargetId] INT NULL,
    [ThresholdForNextAlert] DATETIME2(2) NULL,
    [Scheduled] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Sent] DATETIME2(2) NULL,

    CONSTRAINT [FK_PushSent_ConnectionRequestTargetId_to_ConnectionRequest_Target] 
       FOREIGN KEY ([ConnectionRequestTargetId]) 
       REFERENCES [User].[ConnectionRequest]([Target]), -- This doesn't work
    -- Other FK's and check constraints removed for brevity
);

The ConnectionRequest table contains a history of the connection requests and their status updates. I normalized the target of the connection request into its own table so the target

It seems to me you have an entity Target against which you may have an active ConnectionRequest. It would appear that the Status of a request will be changed from time to time as the request is processed. In that case, that field should not be made part of any key or unique constraint. Only stable (unchanging) fields should be part of a key. In this case, it would appear that the Created field would be perfect.

However, I don't see why you need to create a unique constraint at all. You already assign a unique PK to each request, why don't you have ConnectionRequestTargetId refer to ConnectionRequest( ConnectionRequestID )? This would safely tie each PushSent to one specific request.

StackExchange Database Administrators Q#116836, answer score: 4