外键引用多个表 [英] Foreign Key Referencing Multiple Tables

问题描述

我有一个具有uniqueidentifier的列，可以引用四个不同的表中的一个。我已经看到这样做有两种方式，但是两者看起来都是坏的做法。

I have a column with a uniqueidentifier that can potentially reference one of four different tables. I have seen this done in two ways, but both seem like bad practice.

首先，我看到一个ObjectID列没有明确声明它作为外键一个特定的表。然后你可以推你想要的任何uniqueidentifier。这意味着您可以从不是我想要的4个表的一部分的表中插入ID。

First, I've seen a single ObjectID column without explicitly declaring it as a foreign key to a specific table. Then you can just shove any uniqueidentifier you want in it. This means you could potentially insert IDs from tables that are not part of the 4 tables I wanted.

其次，因为数据可以来自四个不同的表，也看到人做了4个不同的外键。在这样做时，系统依赖于一个AND ONLY ONE列具有非NULL值。

Second, because the data can come from four different tables, I've also seen people make 4 different foreign keys. And in doing so, the system relies on ONE AND ONLY ONE column having a non-NULL value.

这是什么更好的方法？例如，我的表中的记录可能可能引用医院（ID），诊所（ID），学校（ID）或大学（ID）...但只有这些表。

What's a better approach to doing this? For example, records in my table could potentially reference Hospitals(ID), Clinics(ID), Schools(ID), or Universities(ID)... but ONLY those tables.

谢谢！

推荐答案

您可能需要考虑一个Type / SubType数据模型。这非常像面向对象编程中的类/子类，但更尴尬的实现，没有RDBMS（我知道）本来支持他们。一般的想法是：

You might want to consider a Type/SubType data model. This is very much like class/subclasses in object oriented programming, but much more awkward to implement, and no RDBMS (that I am aware of) natively supports them. The general idea is:

• 定义一个类型（建筑），为它创建一个表，给它一个主键


• 您定义两个或多个子类型（这里是医院，诊所，学校，大学），为每个子类型创建表，创建主键...但主键也是引用Building表


• 现在可以使用外键在Building表中构建一个具有一个ObjectType列的表。你必须加入几个表来确定它是什么样的建筑，但你必须这样做。

您注意到此模型的问题，对吗？什么是保持建筑物在两个或更多的子类型表中有条目？很高兴你问：

You have noticed the problem with this model, right? What’s to keep a Building from having entries in in two or more of the subtype tables? Glad you asked:

1. 在Building中添加一个可能是BuildingType的列，


2. 在BuildingID + BuildingType上创建唯一约束


3. 在子表。对它设置检查约束，以便它只能设置为值（医院表的H等）。理论上，这可以是一个计算列;在实践中，由于以下步骤，此操作将无法正常工作：


4. 创建外键以使用这两列来关联表

1. Add a column, perhaps "BuildingType", to Building, say char(1) with allowed values of {H, C, S, U} indicating (duh) type of building.
2. Build a unique constraint on BuildingID + BuildingType
3. Have the BulidingType column in the subtables. Put a check constraint on it so that it can only ever be set to the value (H for the Hospitals table, etc.) In theory, this could be a computed column; in practice, this won't work because of the following step:
4. Build the foreign key to relate the tables using both columns

Voila：如果将BUILDING行设置为类型H，则SCHOOL表中的条目（类型为S）不能设置为引用Building

Voila: Given a BUILDING row set with type H, an entry in the SCHOOL table (with type S) cannot be set to reference that Building

你会记得我说的很难实现。

You will recall that I did say it was hard to implement.

其实，最大的问题是：这是值得吗？如果将建筑类型实现为类型/子类型（进一步的规范化优点：对于每个建筑物通用的地址和其他属性的一个地方，具有存储在子表中的建筑物特定的属性）来实现四个（或更多个）它可能值得额外的努力建立和维护。如果没有，那么你回到方形：在一般的现代RDBMS中很难实现的逻辑模型。

In fact, the big question is: Is this worth doing? If it makes sense to implement the four (or more, as time passes) building types as type/subtype (further normalization advantages: one place for address and other attributes common to every building, with building-specific attributes stored in the subtables), it may well be worth the extra effort to build and maintain. If not, then you’re back to square one: a logical model that is hard to implement in the average modern-day RDBMS.

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