Oracle - 多种数据类型的参照完整性

Question

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

具体来说，我有一堆不同的 table ——假设，假设我有苹果、香蕉、橙子、橘子、葡萄和一百多种水果。现在我正在尝试制作一个表格来描述涉及水果的执行步骤。所以我想插入一行写着“吃 Apple ID 100”，然后是另一行写着“peel Banana ID 250”，然后是另一行写着“refrigerate Tangerine 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 列。这也有效，但没有任何方法(AFAIK)让 Oracle 以任何方式强制执行数据的有效性。所以我们的列将是 ACTION、FRUITTYPE 和 FRUITID。行数据将是 'Eat'、'Apple'、100，然后是 'Peel'、'Banana'、250 等。但是没有什么可以阻止某人删除 Apple ID 100，或者插入一个步骤说 'Eat', 'Apple', 90000000 即使我们没有带有该 ID 的 Apple。

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

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

Answer 1

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

根据我的经验，对此类数据建模的最佳方法是使用父类(super class)型(在您的示例中为 FRUIT)和特定对象(APPLE、GRAPE、BANANA)的子类型。这使我们能够在一个地方存储公共(public)属性，同时记录每个实例的特定属性。

这是父类(super class)型表:

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)
/

---

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

---

"... if you want different tasks for different fruits..."

是的，我想知道这是否是 OP 发布的设计背后的动机。但通常工作流比这困难得多:一些任务将适用于所有水果，一些只适用于(比如)成串的水果，其他只与香蕉相关。

---

"In our actual logic, the 'fruits' are totally disparate tables with very little commonality. Think customers, employees, meetings, rooms, buildings, asset tags, etc. The list of steps is supposed to be free-form and allow users to specify actions on any of these things."

所以你有一堆现有的表。您希望能够以随心所欲的方式将这些表中的记录分配给任务，同时又能够保证识别出拥有该任务的特定记录。

我认为您仍然需要一个通用表来保存任务中参与者的 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)
    )
/

保存 Actor 的通用表格:

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 记录。同上删除。唯一的好消息是您可以生成所需的所有代码。

这个解决方案是否比具有一百个可选外键的表更好？也许不是:这是一个品味问题。但我更喜欢它而不是根本没有外键。如果在数据库实践中有一个普遍的真理，那就是:依赖应用程序代码来强制执行关系完整性的数据库是充斥着引用错误父项或根本没有引用父项的子项的数据库。