I am working with a legacy database that features old and new tables.

One of the new tables looks something like this:
Table: Unit
PK guid
key1
key2

Even though guid is the primary key, older tables in the database use (key1,key2) as a foreign key to the Unit table, since these combinations are known to be unique. On the other hand, new tables use 'guid' to identify each element.

Effectively, I have two alternate primary keys. One single, the other composite.

I'm stuck because if I try to make (guid,key1,key2) a primary key, neither old nor new tables will be able to reference it since they never have all three. If I try to make guid the primary key, then the older tables complain that the foreign key '(key1,key2)' is not a primary key of Unit. If I make (key1,key2) the primary key, then the newer tables will complain that the foreign key 'guid' is not a primary key of Unit.

Advice would be appreciated!

-mv

As I understand you problem you have one physical join table which is actual two logical join tables?

You can divide the physical table into two using database views and mapping the relation from new to old tables through the views as join tables. That is if you database system allows inserts and updates on views.

E.g.





Then use @JoinTable on your object relations.