是否可以将表限制为仅在一组列中的一个中具有值

本文介绍了是否可以将表限制为仅在一组列中的一个中具有值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个表需要链接三个独立表之一，但它只应该链接到其中一个，例如

Main_Table id UNIQUEIDENTIFIER t1_id UNIQUEIDENTIFIER t2_id INT t3_id INT T1 id UNIQUEIDENTIFIER name VARCHAR ） T2 id INT name VARCHAR（255） T3 id INT name VARCHAR（255）

可以有一个约束，t1，t2或t3中只有一个不为空时间？

这只是不好的设计吗？

Main_Table试图成为一个付款人表，它可以引用一个单独的用户（T1），一组个人用户（T2）或一组组（T3）。

这是我继承的数据库设计，更改不幸。

我最大的问题是我需要在不同类型之间关联，因此类型字段不会在这里工作，因为索引不同。

解决方案

您描述的设计称为独占弧。是的，这是一个非常脆弱的设计，甚至不符合一些规范的规范化。

这是一个替代方案：

Main_Table id UNIQUEIDENTIFIER t_id INT NOT NULL FOREIGN KEY（t_id）REFERENCES T0（id） T0 id UNIQUEIDENTIFIER type INT NOT NULL CHECK（type IN（1,2,3）） UNIQUE KEY（id，type） T1 id INT type INT NOT NULL CHECK（type = 1） name VARCHAR（255） FOREIGN KEY（id，type）REFERENCES T0（id，type） T2 id INT type INT NOT NULL CHECK（type = 2） name VARCHAR（255） FOREIGN KEY（id，type）REFERENCES T0（id，type） b $ b T3 id INT 类型INT NOT NULL CHECK（type = 3） name VARCHAR（255） FOREIGN KEY（id，type）REFERENCES T0 ）

使用此设计， Main_Table 必须引用 T0 中的一行。 同样， T0 中的每一行都可以是 T1 ， T2 或 T3 。

这是一种实现类表继承和多态关联而不破坏引用完整性的方法。

Main_Table试图成为付款人表，它可以引用个人用户（T1），一组个人用户（T2）或一组组（T3）。

在面向对象设计方面。如果您有三个可以充当付款接收方的课程，则可以创建一个名为可付款的接口依赖于键入这些对象。所有可支付对象必须有一个 sendPayment（）方法。在一些OO语言中，接口是超类，并被称为抽象类或纯虚拟类。

T0 表用作每个子表的公共类型 T1 ， T2 和 T3 。当 Main_Table 具有 T0 的外键时，就好像 Main_Table 必须引用可支付的某个实体，但从该超类下降的任何对象都可以使用。

类型列只是一个技巧，以确保给定的 T0.id 一次一个子类表。这是一种可选的，如果你可以依靠你的应用程序逻辑插入一个子行只有一个子类表。

另请参阅我的演示文稿中的多态关联部分 SQL Antipatterns Strike Back 。

I have a table which needs to link one of three seperate tables, but it should only link to one of them, e.g.

Main_Table id UNIQUEIDENTIFIER t1_id UNIQUEIDENTIFIER t2_id INT t3_id INT T1 id UNIQUEIDENTIFIER name VARCHAR(255) T2 id INT name VARCHAR(255) T3 id INT name VARCHAR(255)

Is it possible to have a constraint whereby only one of t1, t2 or t3 is not null at any one time?

Is this just bad design? If so, what suggestions would you make for the design?

EDIT:

I've been asked to elaborate the reasons behind this particular design.

Main_Table is attempting to be a payer table, which could reference either an individual user (T1), a group of individual users (T2), or a group of groups (T3).

This is a database design I've inherited, and it isn't really subject to change unfortunately.

My biggest problem is that I need to associate between different types, so a type field won't work here as the indexes are different.

解决方案

The design you're describing is called exclusive arcs. Yes, it's a pretty fragile design and even fails some rules of normalization.

Here's an alternative:

Main_Table id UNIQUEIDENTIFIER t_id INT NOT NULL FOREIGN KEY (t_id) REFERENCES T0 (id) T0 id UNIQUEIDENTIFIER type INT NOT NULL CHECK (type IN (1,2,3)) UNIQUE KEY (id, type) T1 id INT type INT NOT NULL CHECK (type = 1) name VARCHAR(255) FOREIGN KEY (id, type) REFERENCES T0 (id, type) T2 id INT type INT NOT NULL CHECK (type = 2) name VARCHAR(255) FOREIGN KEY (id, type) REFERENCES T0 (id, type) T3 id INT type INT NOT NULL CHECK (type = 3) name VARCHAR(255) FOREIGN KEY (id, type) REFERENCES T0 (id, type)

With this design, each row in Main_Table must reference one row in T0. Likewise, each row in T0 can be the parent of only one row in T1, T2, or T3.

This is a way to implement Class Table Inheritance and Polymorphic Associations without breaking referential integrity.

Main_Table is attempting to be a payer table, which could reference either an individual user (T1), a group of individual users (T2), or a group of groups (T3).

Right, so think of this in terms of object-oriented design. If you had three classes that could function as a recipient of payments, you'd create a interface called Payable or something, so that each you could rely on typing those objects. All Payable objects must have a sendPayment() method for instance. In some OO languages, the interface is a superclass and is called an abstract class or a pure virtual class.

The T0 table functions as a common type for each of the child tables T1, T2, and T3. When Main_Table has a foreign key to T0, it's like saying Main_Table must have a reference to some entity that is Payable, but any object descending from that superclass is okay to use.

The type column is just a trick to make sure that a given T0.id can be referenced only by one subclass table at a time. It's kind of optional, if you can rely on your application logic to insert a given child row into only one of the subclass tables.

Also see the section on Polymorphic Associations in my presentation "SQL Antipatterns Strike Back."