SQL Server foreign key to multiple tables

Question

I have the following database schema:

members_company1(id, name, ...);
members_company2(id, name, ...);
profiles(memberid, membertypeid, ...);
membertypes(id, name, ...)
[ 
       { id : 1, name : 'company1', ... }, 
       { id : 2, name : 'company2', ... }
];

So each profile belongs to a certain member either from company1 or company2 depending on membertypeid value

members_company1     —————————      members_company2     
————————————————                    ————————————————
id      ——————————> memberid <———————————         id
name               membertypeid                 name
                       /|\
                        |  
                        |  
      profiles          |  
      ——————————        |  
      memberid  ————————+  
      membertypeid

I am wondering if it's possible to create a foreign key in profiles table for referential integrity based on memberid and membertypeid pair to reference either members_company1 or members_company2 table records?

Answer 1

A foreign key can only reference one table, as stated in the documentation (emphasis mine):

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables.

But if you want to start cleaning things up you could create a members table as @KevinCrowell suggested, populate it from the two members_company tables and replace them with views. You can use INSTEAD OF triggers on the views to 'redirect' updates to the new table. This is still some work, but it would be one way to fix your data model without breaking existing applications (if it's feasible in your situation, of course)