如何在数据库中模拟标记的并集?

Asked
Viewd2242

11

在数据库中模拟标记的联合的最佳方法是什么? 我说的是这样的:

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

vehicle_id是汽车表或汽车表中的ID,并且知道哪个。

(假设汽车和汽车桌没有什么共同点0

3 个答案

9

某些人使用称为多态关联的设计来执行此操作,从而允许vehicle_id包含carmotor表中存在的值。然后添加一个vehicle_type,用于命名t1中给定行引用的表。

麻烦的是,如果执行此操作,则无法声明真正的SQL外键约束。 SQL不支持具有多个引用目标的外键。还有其他问题,但是缺少参照完整性已经成为破坏交易的事情。

一种更好的设计是从OO设计中借用一个概念,即carmotor通用超类型

 CREATE TABLE Identifiable (
 id SERIAL PRIMARY KEY
);
 

然后使t1引用此超类型表:

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

并使子类型引用其父超类型。请注意,子类型的主键是 not 自动递增。父超类型负责分配新的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的答案还显示了强制执行不相交子类型的方法。也就是说,您要防止carmotor都引用其父超类型表中的同一行。当执行此操作时,我将单列主键用于Identifiable.id,但还要在UNIQUE上声明Identifiable.(id, type)键。carmotor中的外键可以引用两栏唯一键而不是主键。

  • 我自己想出并使用了“普通超型”方法,并在大型系统迁移/重新开发项目中成功使用了该方法。(新西兰政府,教育部的SPOT25)

    Thomas WJune 26, 2012 22:52
  • 仅当查询中需要选择identifiable中的属性时,identifiable的代理键才有用。如果identifiable仅用于强制执行约束,则使用复合键可以在查询中竞争地摆脱它。

    QuassnoiNovember 13, 2009 17:54
3

我认为您可以通过使用表继承PostgreSQL

如果您确实需要知道查询中行的位置,则可以使用简单的UNION ALL语句,例如(这种可能性与表继承无关):

 SELECT car.*, 'car' table_name
UNION ALL
SELECT motor.*, 'motor' table_name
 
6
 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
  ...
)
 
  • 如果您将VEHICLE.VEHICLE_ID定义为主键,则不必再使用复合键,并且类型和ID列使用唯一约束即可使工作变得更轻松。

    OMG PoniesNovember 13, 2009 17:36
  • @OMG Ponies:使用此布局,您完全不需要引用vehicle。您可以只加入carsmotorcycles,具体取决于type。这里的vehicle仅用于管理关系。

    QuassnoiNovember 13, 2009 17:50
  • 使用这种方法,有什么方法可以保证不会出现在carmotorcycle中没有相应行的“孤立”车辆?

    Michael HewsonSeptember 25, 2017 21:31