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) )
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.