主页 / dba / 问题 / 267173
Accepted
Kirk Saunders
Asked: 2020-05-15 13:55:35 +0800 CST

参照完整性——“深度”中的间接外键

  • 772

问题

当从多个关系树创建“子”或“孙子”表时，确保维护“祖父”或“曾祖父”表中的键的最佳实践是什么。

详细信息，因为该问题可能没有意义

我正在尝试建立一个数据库来跟踪我们环境中运行的自动化进程的执行状态。

一般来说，我们有一个“作业”，它触发一个或多个“可执行文件”，这些“可执行文件”可以为一个或多个客户运行任务。然后我们将有 2 个记录表，一个跟踪“作业”何时启动，另一个记录每个“ExecutableCustomer”实例的成功与失败状态。

计划的简化模式如下： ER图

当我们将记录正确到 时JobExecutableCustomerExecutionLog，我想确保与Job.IDvalue 关联的值与与关联的JobExecutionLog.ID值匹配。Job.IDJobExecutableCustomer.ID

通常我会用 aForeign Key但是因为Job.ID不存储在JobExecutableCustomer,JobExecutableCustomerExecutionLog也不JobExecutionLog. 关系是间接的。

例子：

我有 2 个工作，“发送电子邮件”和“发送短信”。“发送电子邮件”启动属于 1 个客户的单个可执行文件。“发送文本消息”有 2 个可执行文件（两者都为同一个客户执行）。我想确保当记录被写入JobExecutableCustomerExecutionLog“发送电子邮件”时，Job.ID关联JobExecutableCustomerExecutionLog.JobExecutableCustomerID和JobExecutableCustomerExecutionLog.JobExecutionLogID（在关系上升之后）实际上属于Job.ID“发送电子邮件”而不是“发送短信”。

正如我所看到的，我有两个选择：

  1. 将值从Job.ID所有子表中推入，并使其成为Foreign Key
  2. 让另一个过程（Trigger或Indexed View）为我确保关系

我个人不喜欢Job.ID在所有其他子表上推送值的想法，所以我倾向于使用 aTrigger或其他东西来处理它。我不知道这是否是我仅有的两个选择，或者我是否有能力配置“正常”Foreign Key来遍历关系。在某种Cascade或别的什么。

sql-server database-design

2 个回答

  1. Best Answer

    我个人不喜欢在所有其他子表上推送 Job.ID 值的想法

    为什么不？显而易见的解决方案是让 JobID 成为所有子表上的前导 PK/聚集索引列。它确保通过 JobID 访问所有这些表的最佳性能。

    通常，只要您有“标识关系”又名“子表”又名“弱实体”，子表应使用复合主键，其前导列也是父表的外键。像这样的东西：

    create table Parent
(
  ParentId int not null identity,
  constraint pk_Parent
    primary key(ParentId)
)

create table Child
(
  ParentId int not null, 
  ChildId int not null identity, 
  constraint pk_Child 
    primary key (ParentId,ChildId),
  constraint fk_Child_Parent
    foreign key (ParentId) references Parent(ParentId)
    on delete cascade
)
create table GrandChild
(
  ParentId int not null references Parent, 
  ChildId int not null, 
  GrandChildId int not null identity,
  constraint pk_GrandChild 
    primary key (ParentId,ChildId,GrandChildId),
  constraint fk_GrandChild_Child
    foreign key (ParentId) references Parent(ParentId)
    on delete cascade
)
    • 3

  2. 如何使用FKs. _ 我对模型进行了一些修改，以允许可执行文件成为多个作业的一部分。假设是，当一个可执行文件运行时，它会为与该可执行文件关联的所有客户运行。您的 ERD 没有显示约束，因此涉及一些猜测。 

    -- Customer CUS exists.
--
customer {CUS}
      PK {CUS}

    -- Executable EXE exists.
--
executable {EXE}
        PK {EXE}

    -- Executable EXE runs task for customer {CST}.
--
customer_exe {EXE, CST}
          PK {EXE, CST}

FK1 {EXE} REFERENCES executable {EXE}
FK2 {CUS} REFERENCES customer   {CUS}

    -- Job JOB exists.
--
job {JOB}
 PK {JOB}

    -- Step number STP# of job JOB
-- runs executable EXE.
--
job_step {JOB, STP#, EXE}
      PK {JOB, STP#}
      SK {JOB, STP#, EXE}

FK1 {JOB} REFERENCES job {JOB}
FK2 {EXE} REFERENCES executable {EXE}

    一个假设是任务写入log_表，因此预计会有一些冗余：比如EXEin log_job; 仍然FKs防止异常。

    -- Executable EXE ran as step number STP#
-- of job JOB on DTE (date-time).
--
log_job {DTE_J, JOB, STP#, EXE}
     PK {DTE_J, JOB, STP#}
     SK {DTE_J, JOB, STP#, EXE}

      FK {JOB, STP#, EXE} REFERENCES
job_step {JOB, STP#, EXE}

    -- Executable EXE ran for customer CUS,
-- with success result RES, on DTE_C (date-time),
-- as step number STP# of job JOB, which ran on DTE_J.
--
log_cus {DTE_J, JOB, STP#, CUS, EXE, DTE_C, RES}
     PK {DTE_J, JOB, STP#, CUS}

    FK1 {DTE_J, JOB, STP#, EXE} REFERENCES
log_job {DTE_J, JOB, STP#, EXE}

         FK2 {EXE, CST} REFERENCES
customer_exe {EXE, CST}

    笔记：

    All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key
    • 2

相关问题