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)

Answer 2

Operating under the fact that you can't change the table structure:

Option 1

How important is referential integrity to you? Are you only doing inner joins between these tables? If you don't have to worry too much about it, then don't worry about it.

Option 2

Ok, you probably have to do something about this. Maybe you do have inner joins only, but you have to deal with data in profiles that doesn't relate to anything in the members tables. Could you create a job that runs once per day or week to clean it out?

Option 3

Yeah, that one may not work either. You could create a trigger on the profiles table that checks the reference to the members tables. This is far from ideal, but it does guarantee instantaneous checks.

My Opinion

I would go with option 2. You're obviously dealing with a less-than-ideal schema. Why make this worse than it has to be. Let the bad data sit for a week; clean the table every weekend.

Answer 3

A foreign key cannot reference two tables. Assuming you don't want to correct your design by merging members_company1 and members_company2 tables, the best approach would be to:

Add two columns called member_company1_id and member_company2_id to your profiles table and create two foreign keys to the two tables and allow nulls. Then you could add a constraint to ensure 1 of the columns is null and the other is not, at all times.

Answer 4

No. A foreign key can reference one and only one primary key and there is no way to spread primary keys across tables. The kind of logic you hope to achieve will require use of a trigger or restructuring your database so that all members are based off a core record in a single table.

Answer 5

Come on you can create a table but you cannot modify members_company1 nor members_company2?

Your idea of a create a members table will require more actions when new records are inserted into members_company tables.
So you can create triggers on members_company1 and members_company2 - that is not modify?

What are the constraints to what you can do?

If you just need compatibility on selects to members_company1 and members_company2 then create a real members table and create views for members_company1 and members_company2.
A basic select does not know it is a view or a table on the other end.

CREATE VIEW dbo.members_company1
AS
SELECT id, name 
FROM members
where companyID = 1

You could possible even handle insert, updates, and deletes with instead-of

INSTEAD OF INSERT Triggers