与多个表中的一个建立一对一关系

Question

好的，我希望我能够弄清楚我的问题是什么： 我有一个有 5 个表的数据库。我们将它们称为 A 和 B、V_1、V_2 和 V_3。 A 和 B 代表要完成的事情列表。 V_i 表中描述了这些操作。现在，A 代表某种必须使用某种类型的项目完成的内容模板。另一方面，如果 A 描述了抽象项，则 B 描述了必须用具体实例完成（或已经完成）的操作。因此，在 OOP 术语中，人们可能会说 A 代表一个类，B 代表 A 的一个实例每当向表 B 中插入某些内容时，都会复制表 A 中的相关数据，以便可以针对该特定项进行修改而不影响 A。

好的，这就是实际问题：如何正确建模？我主要关心的是 V_i 中的每条记录不得同时链接到 A 和 B。它必须与 A 或 B 建立 1 对 1 的关系。此外，V_i 和 V_j 不得链接到 A 或 B 中的同一记录B. 我不知道如何正确地做到这一点。当前的结构如下所示：

A和B有一个称为ID的PK。每个V_i还有一个称为ID的PK和两个引用A或B的FK，我们称它们为A_ID和B_ID。现在，当前的实现确保 A_ID 或 B_ID 为 NULL，但不能同时为 NULL。但是，我想知道是否有更好的方法来做到这一点。此外，还存在多个 V_i 可以引用 A 或 B 中相同条目的问题。

所以，我希望我的问题是清楚的。有没有一种方法可以使用关系数据库对此进行正确建模，而不依赖外部代码来强制执行约束？感谢您提前提供意见。

此致 大卫

Answer 1

在关系理论中，一对一关系通常被转换为物理模型中的单个表。该单个表将包含两个表中的行，您将使用检查约束来确定行的类型。这是迄今为止获得可靠的一对一关系的最简单方法。

Answer 2

首先：设计数据库时，您表达的是记录之间的关系而不是表之间的关系。
您正在用面向对象的观点表达您的问题。这种范例不能用于设计表（SQL 是一种声明性语言）。

否则，您可以在表上添加约束以确保您的谓词。

也许 Oracle 提供了我不知道的其他可能性。

Answer 3

在rdbs中建模类-实例关系的最常见方法是
类=表
Instance = row

考虑一下：为每个新实例插入一个新行；如果您不插入数据，则会插入默认值，这将为您提供类数据；和触发器给你班级级别的行为。

或者，给 A 和 B 相同的主键，并将 B 的 PK 设置为 A 的 PK 的 FK。当 B 中包含一行时，DBMS 将检查 A 中是否存在“父”行。需要绘图

+--------+   +--------+
|Table A |   |Table B |
+--------+   +--------+
|id (PK) |<--|id* (PK)|
|col1    |   |colB1   |
| ...    |   | ...    |
+--------+   +--------+

Answer 4

前言：正如其他人指出的那样，这是一个糟糕的设计。

假设：

create table a (a_id number primary key); 

create table b (b_id number primary key); 

create table v1 
(v1_id number primary key, a_id number references a, b_id number references b);

create table v2
(v2_id number primary key, a_id number references a, b_id number references b);

create table v3
(v3_id number primary key, a_id number references a, b_id number references b);

强制在任何 V_i 表中只需要 A 或 B 中的一个 id（但不是两者）是非常容易的。

alter table V1
  add constraint v1_check check
  (    (a_id is null and b_id is not null)
    or (a_id is not null and b_id is null)
  );

如果您想扩展该约束，以便 A 或 B 中的 id 之一准确存在，并且该值存在于一行且仅一行中：

create unique index v1_check_unique on v1  ( coalesce (a_id, b_id) );

困难的部分是确保 A 和 B 中的 id 存在且仅一行中V_i 表的。这无法在 DML 时完成，但可以在提交时强制执行。

create materialized view log on v1 with rowid;
create materialized view log on v2 with rowid;
create materialized view log on v3 with rowid;
CREATE MATERIALIZED VIEW CROSS_TABLE
REFRESH FAST ON COMMIT
AS
  SELECT V1_ID AS V_ID, 'V1' AS TABLE_NAME, ROWID AS ROW_ID, 
         COALESCE (A_ID, B_ID) AS OTHER_ID FROM V1
  UNION ALL
  SELECT V2_ID AS V_ID, 'V2' AS TABLE_NAME, ROWID AS ROW_ID, 
         COALESCE (A_ID, B_ID) AS OTHER_ID FROM V2
  UNION ALL
  SELECT V3_ID AS V_ID, 'V3' AS TABLE_NAME, ROWID AS ROW_ID, 
         COALESCE (A_ID, B_ID) AS OTHER_ID FROM V3
/

ALTER TABLE CROSS_TABLE ADD CONSTRAINT CROSS_TABLE_UNIQUE UNIQUE (OTHER_ID);

这似乎有效——但没有你希望的那么好。 Oracle 无法在语句时强制执行表的唯一性，因为不允许会话 A 考虑其他会话可能进行的任何其他更改。它只能在提交时强制执行该唯一性。

以下测试用例在针对空表运行时失败 - 并回滚整个事务，因为它无法推断出导致失败的原因。买者自负。

INSERT INTO A VALUES (1);
INSERT INTO B VALUES (1);
INSERT INTO V1 (V1_ID, A_ID, B_ID) VALUES (1, 1, NULL);
INSERT INTO V2 (V2_ID, A_ID, B_ID) VALUES (1, 1, NULL);
COMMIT;