Foreign key referencing table with composite primary key when I only need 1 column association So lets say we have 2 tables on SQL Server database:

CREATE TABLE VersionedData (
    Id INT NOT NULL,
    Version INT NOT NULL

    CONSTRAINT [PK_VerData]
      PRIMARY KEY (Id, Version)
)


And:

CREATE TABLE PointingTable (
    Id INT PRIMARY KEY,
    RefId INT NOT NULL

    CONSTRAINT [FK_Pnt_Ver] FOREIGN KEY (RefId)
      REFERENCES VersionedData (Id)
)


My idea of this relationship is when joining these 2 particular tables I only care about latest version of VersionedData table (meaning I will ALWAYS match with maximum version for specific ID value giving me exactly 1 row that I need).

Of course the code above causes error that I must reference both PK columns from PointingTable. I could add Version column for PointingTable - however I'd be stuck to that specific version even when newer one will be inserted for VersionedTable.

I'm struggling to figure out how to redesign this relationship, I've thought of computed column for PointingTable that would utilize scalar function for getting max version, but I think that would be overkill, there must be a better way.
The alternative is to create an additional table

CREATE TABLE CurrentVersions (
    Id INT PRIMARY KEY,
    Version INT
)


with appropriate foreign key relations to VersionedData. You can then have the foreign key FK_Pnt_Ver reference CurrentVersions instead.

You'll either need an extra JOIN to fetch the data from VersionedData (I assume it contains additional columns), or alternative store the data you need in CurrentVersions as well. In that case, you don't even need the Version column.

This situation is similar to how Stack Exchange stores posts and revisions. Their database looks very much like the public Data Explorer whose schema is documented here on Meta. The Posts table contains the current version of a post as HTML (Body) and the revisions are stored (as Markdown) in the Text column of the PostHistory table.