是否可以将表限制为仅在一组列中的一个中具有值 [英] Is it possible to constrain a table to have a value in only one of a set of columns

问题描述

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

  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."

这篇关于是否可以将表限制为仅在一组列中的一个中具有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！