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?

Overlying

Seems like you need to have a company_id in the members table instead of keeping a members table for each company. – Pizza

@KevinCrowell I know, good point, but I'm working with what I have so let's assume I have no control over members_company1 and members_company2 tables. The only ugly solution I have in mind right now is to create members table that would store id's and types from these 2 tables, but it kind of duplicates data and will require more actions when new records are inserted into members_company tables. – Overlying

A foreign key can only reference one table. 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). – Allowable

@Pondlife, make it an answer and I'll accept it. First sentence in your comment is the answer I was looking for, but triggers on views is actually a good idea. – Overlying

Just commenting to say that I like your illustration. – Canter

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)

Allowable

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.

Empty

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.

Trichocyst

Would need to also assure the value in member_companyX_id matches the value in id. But it might work. – Bordelon

@Blam That is satisfied with the foreign key constraint. – Trichocyst

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.

Placard

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

Bordelon

That's correct, I can't. But the question is not about triggers and additional tables, my concern was about a foreign key constraint on more than two tables. – Overlying

Comment "The only ugly solution I have in mind right now is to create members table that would store id's and types from these 2 tables, but it kind of duplicates data and will require more actions when new records are inserted into members_company tables." That is an additional table and triggers. What is the real constraint? – Bordelon

it wasn't part of the question, but if you want discuss my future plans welcome to the comment section above. – Overlying

Cool, but if tables and triggers were out of scope then you should not have introduced them as even an ugly solution. – Bordelon

I was talking to the other person who introduced an idea of a separate table/design in the comments section. Anyways judging by your tone (even after your edits) I can tell you're here just to argue or troll. – Overlying

I don't even know what troll is. But I do know if I have authority to add table to database or not or modify a table. SO is for specific programming questions. – Bordelon