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

问题描述

我有以下数据库架构:

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

因此，每个个人资料都属于来自 company1 或 company2 的某个成员，具体取决于 membertypeid 值

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

我想知道是否可以基于 memberid 和 membertypeid 对在 profiles 表中创建一个外键以实现引用完整性members_company1 或 members_company2 表记录?

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):

外键 (FK) 是使用的列或列组合在两个表中的数据之间建立和强制链接.

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.

但是，如果您想开始清理工作，您可以按照@KevinCrowell 的建议创建一个 members 表，从两个 members_company 表中填充它并用视图替换它们.您可以在视图上使用 INSTEAD OF 触发器将更新重定向"到新表.这仍然是一些工作，但这是在不破坏现有应用程序的情况下修复数据模型的一种方法(当然，如果在您的情况下可行)

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屋！