Oracle - 多种数据类型的引用完整性

Question

我正在 Oracle 中处理一组数据库表，并试图找到一种方法来通过稍微多态的数据强制执行引用完整性。

具体来说，我有一堆不同的桌子——假设我有苹果、香蕉、橙子、橘子、葡萄和一百多种水果。现在我正在尝试制作一个表格来描述涉及水果的执行步骤。因此，我想插入一行“吃 Apple ID 100”，然后插入另一行“剥香蕉 ID 250”，然后插入另一行“冷藏橘子 ID 500”，依此类推。

从历史上看，我们通过两种方式做到这一点：

1 - 为每种可能的水果类型添加一列。使用检查约束来确保除一列之外的所有列均为 NULL。使用外键来确保我们的水果的引用完整性。因此，在我假设的示例中，我们有一个包含 ACTION、APPLEID、BANANAID、ORANGEID、TANGERINEID 和 GRAPEID 列的表。对于第一个操作，我们有一行 'Eat', 100, NULL, NULL, NULL, NULL, NULL。对于第二个操作，我们有'Peel', NULL, 250, NULL, NULL, NULL。 这种方法非常适合

自动获得 Oracle 的所有 RI 优势，但它无法扩展到一百种水果。您最终会得到太多的列而不实用。仅仅弄清楚您正在处理的是哪种水果就成了一个挑战。

2 - 包括包含水果名称的列和包含水果 ID 的列。这也有效，但是（据我所知）没有任何方法可以让 Oracle 以任何方式强制执行数据的有效性。因此，我们的列将是 ACTION、FRUITTYPE 和 FRUITID。行数据将是 'Eat', 'Apple', 100，然后是 'Peel', 'Banana', 250 等。但是没有什么可以阻止某人删除 Apple ID 100，或插入一个步骤，表示'Eat', 'Apple', 90000000，即使我们没有具有该 ID 的 Apple。

有没有办法避免为每种水果类型维护一个单独的列，但仍然保留外键的大部分好处？ （或者从技术上讲，如果我能用某种巧妙的技巧隐藏复杂性，我可以确信使用一百列。它只需在日常使用中看起来很正常即可。）

澄清：在我们的实际逻辑中，“水果”是完全不同的桌子，几乎没有共同点。想想客户、员工、会议、房间、建筑物、资产标签等。步骤列表应该是自由格式的，并允许用户指定对任何这些事物的操作。如果我们有一个表包含所有这些不相关的东西，我不会有问题，但它也会是一个非常奇怪的设计。

Answer 1

我不清楚为什么您需要识别 TASKS 表上的 FRUIT_TYPE 。从表面上看，这只是一个糟糕的（非规范化的）数据模型。

根据我的经验，对此类数据进行建模的最佳方法是使用通用事物的超类型（示例中的水果）和特定事物的子类型（苹果、葡萄、香蕉）。这允许我们将公共属性存储在一处，同时记录每个实例的特定属性。

这是超类型表：

create table fruits
    (fruit_id number not null
         , fruit_type varchar2(10) not null
         , constraint fruit_pk primary key (fruit_id)
         , constraint fruit_uk unique (fruit_id, fruit_type)
         , constraint fruit_ck check (fruit_type in ('GRAPE', 'APPLE', 'BANANA'))
    )
/

FRUITS 有一个主键和一个复合唯一键。我们需要在外键约束中使用主键，因为复合键是一个令人头疼的问题。除非它们不是，这就是这些子类型表的情况。这里我们使用唯一键作为参考，因为通过约束子类型中 FRUIT_TYPE 的值，我们可以保证 GRAPES 表中的记录映射到类型为“GRAPE”的 FRUITS 记录等。

create table grapes
    (fruit_id number not null
         , fruit_type varchar2(10) not null  default 'GRAPE'
         , seedless_yn  not null char(1) default 'Y'
         , colour varchar2(5) not null
         , constraint grape_pk primary key (fruit_id)
         , constraint grape_ck check (fruit_type = 'GRAPE')
         , constraint grape_fruit_fk foreign key (fruit_id, fruit_type)
                references fruit  (fruit_id, fruit_type)
         , constraint grape_flg_ck check (seedless_yn in ('Y', 'N'))
    )
/

create table apples
    (fruit_id number not null
         , fruit_type varchar2(10) not null
         , apple_type  varchar2(10) not null default 'APPLE'
         , constraint apple_pk primary key (fruit_id)
         , constraint apple_ck check (fruit_type = 'APPLE')
         , constraint apple_fruit_fk foreign key (fruit_id, fruit_type)
                references fruit  (fruit_id, fruit_type)
         , constraint apple_type_ck check (apple_type in ('EATING', 'COOKING', 'CIDER'))
    )
/

create table bananas
    (fruit_id number not null
         , fruit_type varchar2(10) not null default 'BANANA'
         , constraint banana_pk primary key (fruit_id)
         , constraint banana_ck check (fruit_type = 'BANANA')
         , constraint banana_fruit_fk foreign key (fruit_id, fruit_type)
                references fruit  (fruit_id, fruit_type)
    )
/

在 11g 中，我们可以将 FRUIT_TYPE 设为子类型的虚拟列并消除检查约束。

所以，现在我们需要一个任务类型表（“剥皮”、“冷藏”、“吃”等）。

create table task_types
    (task_code varchar2(4) not null
     , task_descr varchar2(40) not null
     , constraint task_type_pk primary key (task_code)
    )
/

实际的 TASKS 表是 FRUITS 和 TASK_TYPES 之间的简单交集。

create table tasks
    (task_code varchar2(4) not null
     , fruit_id number not null
     , constraint task_pk primary key (task_code, fruit_id)
     , constraint task_task_fk ask foreign key (task_code)
            references task_types (task_code)
     , constraint task_fruit_fk foreign key (fruit_id)
            references fruit (fruit_id)
/

---

如果这不能满足您的需求，请编辑您的问题以包含更多信息。

---

“...如果您想为不同的水果执行不同的任务...”

是的，我想知道这是否是OP发布设计背后的动机。但通常工作流程比这要困难得多：有些任务适用于所有水果，有些任务仅适用于（例如）成串的水果，其他任务仅与香蕉相关。

---

“在我们的实际逻辑中，‘水果’是完全不同的表格，
共性很少。想想客户、员工、会议、房间、
建筑物、资产标签等。步骤列表应该是
自由格式并允许用户指定对任何这些事物的操作。”

因此，您有一堆现有表。您希望能够以随心所欲的方式将这些表中的记录分配给任务，同时能够保证识别 我认为您仍然需要一个通用

表来保存任务中参与者的 ID，但您需要以某种方式将其链接到其他表：

Soem 示例。现有表：

create table customers
    (cust_id number not null
         , cname varchar2(100) not null
         , constraint cust_pk primary key (fruit_id)
    )
/

create table employees
    (emp_no number not null
         , ename varchar2(30) not null
         , constraint emp_pk primary key (fruit_id)
    )
/

用于容纳参与者的通用表：

create table actors
    (actor_id number not null
         , constraint actor_pk primary key (actor_id)
    )
/

现在，您需要交集表将现有表与新表关联起来：

create table cust_actors
    (cust_id number not null
         , actor_id number not null
         , constraint cust_actor_pk primary key (cust_id, actor_id)
         , constraint cust_actor_cust_fk foreign key (cust_id)
                references customers (cust_id)
         , constraint cust_actor_actor_fk foreign key (actor_id)
                references actors (actor_id)
    )
/

create table emp_actors
    (emp_no number not null
         , actor_id number not null
         , constraint emp_actor_pk primary key (emp_no, actor_id)
         , constraint emp_actor_emp_fk foreign key (emp_no)
                references eployees (emp_no)
         , constraint cust_actor_actor_fk foreign key (actor_id)
                references actors (actor_id)
    )
/

考虑到之前发生的情况，TASKS 表相当不足为奇：

create table tasks
    (task_code varchar2(4) not null
     , actor_id number not null
     , constraint task_pk primary key (task_code, actor_id)
     , constraint task_task_fk ask foreign key (task_code)
            references task_types (task_code)
     , constraint task_actor_fk foreign key (actor_id)
            references actors (actor_id)
/

我同意所有这些交集表看起来开销很大，但没有任何其他方法来强制执行外键约束额外的障碍是每次在 CUSTOMERS 中创建删除记录时都会创建 ACTORS 和 CUSTOMER_ACTORS 记录。唯一的好消息是您可以生成所需的所有代码

。一个有一百个可选外键的表？也许不是：这是一个品味问题。但我更喜欢它，而不是完全没有外键。如果数据库实践中存在普遍真理的话，那就是：依赖应用程序代码来强制关系完整性的数据库是充满了引用错误父级或根本不引用父级的子级的数据库。