SQL Server外键到多个表 [英] SQL Server foreign key to multiple tables

问题描述

  members_company1（id，name，...）; 
 members_company2（id，name，...）; 
配置文件（memberid，membertypeid，...）; 
 membertypes（id，name，...）
 [
 {id：1，name：'company1'，...}，
 {id：2，name： 'company2'，...} 
];因此，每个配置文件属于来自company1或company2的某个成员，具体取决于  
 
 
 
 
> membertypeid  value  
 
 
  members_company1 --------- members_company2 
  - -------------- ---------------- 
 id ----------> memberid< ----------- id 
名称membertypeid名称
 / | \ 
 | 
 | 
个人资料| 
 ---------- | 
 memberid -------- + 
 membertypeid 
  
我是想知道是否可以根据 memberid 和 profiles 表中创建一个外键，以实现参照完整性> membertypeid  pair来引用members_company1或members_company2表记录？
解决方案
外键只能引用一个表，如文档（重点是我的）：
 
lockquote 
一个外键（FK）是用
来建立的列或列组合并强制两个表中的数据之间的链接。
 
 
但是，如果你想开始清理你可以像@KevinCrowell建议的那样创建一个成员表，从两个 members_company  t把它们换成意见。您可以在视图上使用 INSTEAD OF 触发器将更新重定向到新表格。这仍然是一些工作，但这将是修复您的数据模型的一种方式，而不会破坏现有的应用程序（当然，如果您的情况是可行的话）。 
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?
解决方案
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)

                        
这篇关于SQL Server外键到多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！