SQL Server foreign key for 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', ... } ]; 

Thus, each profile belongs to a specific member, either from company1 or from company2, depending on membertypeid value

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

I am wondering if it is possible to create a foreign key in the profiles table for referential integrity based on the memberid and membertypeid to reference the members_company1 or members_company2 entries?

Answer 1

A foreign key can refer to only one table, as indicated in the documentation (highlighted by me):

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

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

Answer 2

Work on the fact that you cannot change the structure of the table:

Option 1

How important is referential integrity? Are you just making internal joins between these tables? If you don’t need to worry too much about it, then don’t worry about it.

Option 2

Well, you probably have to do something. You may only have internal joins, but you have to deal with data in profiles that are not related to anything in the member tables. Can you create a task that runs once a day or a week to clear it?

Option 3

Yes, that won't work either. You can create a trigger in the profile table that checks the link to the member tables. This is far from ideal, but it guarantees instant checks.

My opinion

I would go with option 2. You are obviously dealing with a more than ideal circuit. Why make it worse than it should be. Let the bad data sit for a week; clean the table every weekend.

Answer 3

You can create a table, but you cannot change members_company1 or members_company2?

Your idea of ​​creating a member table will take more action when new entries are inserted into the members_company tables.
This way you can create triggers for members_company1 and members_company2 - which are not changing?

What are the limitations of what you can do?

If you just need compatibility when choosing for members_company1 and members_company2, then create a table of real members and create views for members_company1 and members_company2.
The base selection does not know that it is a view or a table at the other end.

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

You can even handle inserts, updates, and deletions instead

INSTEAD OF INSERT Triggers

Answer 4

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

Add two columns named member_company1_id and member_company2_id to the profiles table and create two foreign keys for the two tables and enable nulls . Then you can add a constraint so that 1 of the columns is null and the other is not always.

Answer 5

No. A foreign key can refer to one and only one primary key, and there is no way to distribute primary keys in tables. The logic you hope to achieve will require the use of a trigger or restructuring of your database so that all participants are based on the master record in one table.