如何在数据库中模拟标记联合？

Question

如何在数据库中最好地模拟标记联合？我指的是像这样的东西：

create table t1 {
  vehicle_id INTEGER NOT NULL REFERENCES car(id) OR motor(id) -- not valid
  ...
}

vehicle_id将是汽车表或电机表中的id，并且它会知道应该使用哪个表。

（假设电机表和汽车表之间没有共同点）

- Łukasz Lew

Answer 1

有些人使用多态关联（Polymorphic Associations）的设计来实现此目的，允许vehicle_id包含一个在car表或motor表中存在的值。然后添加vehicle_type，指定给定行在t1引用的表的名称。

问题是如果这样做，您无法声明真正的SQL外键约束。SQL不支持具有多个引用目标的外键。还有其他一些问题，但缺乏引用完整性已经成为一个无法解决的问题。

更好的设计是借鉴OO设计的一个共同超类型（common supertype），即car和motor的共同父类：

CREATE TABLE Identifiable (
 id SERIAL PRIMARY KEY
);

然后让 t1 引用这个超类型表：

CREATE TABLE t1 (
  vehicle_id INTEGER NOT NULL,
  FOREIGN KEY (vehicle_id) REFERENCES identifiable(id)
  ...
);

还要让子类型引用它们的父超类型。请注意，子类型的主键不是自动递增的。父超类型负责分配新的id值，而子级只引用该值。

CREATE TABLE car (
  id INTEGER NOT NULL,
  FOREIGN KEY (id) REFERENCES identifiable(id)
  ...
);

CREATE TABLE motor (
  id INTEGER NOT NULL,
  FOREIGN KEY (id) REFERENCES identifiable(id)
  ...
);

现在你可以拥有真正的引用完整性，同时支持具有自己属性的多个子类型表。

---

@Quassnoi 的回答还展示了一种实施 不交子类型 的方法。也就是说，你希望防止car和motor同时引用它们父级超类型表中的同一行。当我这样做时，我使用单列主键Identifiable.id，但也声明了一个Identifiable.(id, type)的UNIQUE键。在car和motor中的外键可以引用这两列唯一键而非主键。

- Bill Karwin

---

当查询需要选择identifiable中的属性时，identifiable的替代键才是有效的。如果identifiable仅用于强制执行约束，则使用复合键将允许在查询中完全摆脱它。 - Quassnoi

我自己想出并使用了“通用超类型”方法，并在重大系统迁移/重新开发项目中成功地使用它。（新西兰政府，MoE的SPOT25） - Thomas W

Answer 2

CREATE TABLE vehicle (type INT NOT NULL, id INT NOT NULL,
             PRIMARY KEY (type, id)
)

CREATE TABLE car (type INT NOT NULL DEFAULT 1, id INT NOT NULL PRIMARY KEY,
             CHECK(type = 1),
             FOREIGN KEY (type, id) REFERENCES vehicle
)

CREATE TABLE motorcycle (type INT NOT NULL DEFAULT 2, id INT NOT NULL PRIMARY KEY,
             CHECK(type = 2),
             FOREIGN KEY (type, id) REFERENCES vehicle
)

CREATE TABLE t1 (
  ...
  vehicle_type INT NOT NULL,
  vehicle_id INT NOT NULL,
  FOREIGN KEY (vehicle_type, vehicle_id) REFERENCES vehicle
  ...
)

- Quassnoi

---

如果您将VEHICLE.VEHICLE_ID定义为主键，那么您就不必引用复合键，并且可以使用唯一约束条件来设置类型和ID列，这将使生活更加轻松。 - OMG Ponies

@OMG Ponies：使用这种布局，您根本不需要引用“vehicle”。您只需根据“type”与“cars”或“motorcycles”连接即可。在这里，“vehicle”仅用于监管关系。 - Quassnoi

使用这种方法，有没有什么方法可以保证不会有“孤儿”车辆，没有对应的行在“car”或“motorcycle”中？ - Michael Hewson

Answer 3

我认为最少样板代码的解决方案是使用constraint和check。

例如，考虑在 Haskell 中使用的这个 ADT:

data Shape = Circle {radius::Float} | Rectangle {width::Float, height::Float}

MySQL/MariaDB 中的等效方法为（在 10.5.11-MariaDB 上测试过）：

CREATE TABLE shape (        
  type ENUM('circle', 'rectangle') NOT NULL,
  radius FLOAT,             
  width FLOAT,              
  height FLOAT,             
  CONSTRAINT constraint_circle CHECK 
    (type <> 'circle' OR radius IS NOT NULL),
  CONSTRAINT constraint_rectangle CHECK 
    (type <> 'rectangle' OR (width IS NOT NULL AND height IS NOT NULL))
);                                      
                                        
INSERT INTO shape(type, radius, width, height)
  VALUES ('circle', 1, NULL, NULL); -- ok
                                                                                                                    
INSERT INTO shape(type, radius, width, height)
  VALUES ('circle', NULL, 1, NULL); -- error, constraint_circle violated

请注意，上述使用的是type <> x OR y而不是type = x AND y。这是因为后者基本上意味着所有行必须具有x的type，这违背了标记联合的目的。

此外，请注意，上面的解决方案仅检查必需的列，但不检查多余的列。

例如，您可以插入一个定义了radius的rectangle。

这可以通过为constraint_rectangle添加另一个条件来轻松解决，即radius is null。

然而，我不建议这样做，因为它使添加新的type变得繁琐。

例如，要添加一个名为triangle的新的type和一个新列base，不仅需要添加一个新的约束条件，还需要修改现有的约束条件以确保它们的base为空。

- Wong Jia Hau

---

顺便说一句：说 type <> x OR y 实际上等同于蕴含式：type = x => y。另一个等价的（可能更易理解）表达方式是 NOT (type = x AND NOT y)。也就是说，不允许成为圆形但没有半径。你的版本使用德摩根定律将其转换为更紧凑的形式。 - Robin Koch

Answer 4

我认为你可以使用PostgreSQL中的表继承来建立这样一个参考模型。

如果你真的需要知道查询中一行来自哪里，你可以使用简单的UNION ALL语句（这种可能性与表继承无关）：

SELECT car.*, 'car' table_name
UNION ALL
SELECT motor.*, 'motor' table_name

- Karl Bartel