如何检查主表的记录是否被子表使用/引用

Question

在我的 ERP 应用程序中，我将一个名为“IsRemoved”的额外字段作为布尔值。每当用户删除任何记录时，该记录都不会被删除 - 只有其“IsRemoved”列的值为“true”。它可以帮助我们在需要时恢复数据，并且工作正常。

问题是，当用户删除主表的记录时，我们如何检查其所有子表都没有引用该记录（因为我们不执行物理删除，我们只是将“isremoved”字段标记为true）？

请向我提供任何查询或 sp，我可以从中检查主记录是否在其任何子记录中使用。

Answer 1

根据经验，我必须告诉你这个设计很难使用。考虑更改设计以将数据复制到“审核跟踪”表，然后从主表中物理删除它。

如果您不考虑这一点，至少将其隐藏在 VIEW 中，并尽一切努力避免将其暴露给任何想要查询数据库的人，使用 INSTEAD OF > 如有必要，在 VIEW 上触发。否则，应用程序可能会频繁出现错误，因为有人忘记添加使用此表的每个查询所需的AND isremoved = 0谓词。

但是这个“答案”并没有解决真正的问题。

是的。对此感到抱歉。但有时你必须治愈疾病，而不仅仅是治疗症状。

设计是妥协的：表应该对单个实体类型进行建模，而这是对两个实体类型进行建模。我怎么知道？因为OP已经声明，一旦“删除”实体就有不同的数据要求，通过说“问题是......我们如何检查其所有子表不引用此记录”。

所以“真正的”答案是：将实体移动到另一个不同的表。

但是，如果您从事治疗症状的业务，那么这里有一个答案：

1. 将 IsRemoved 列添加到您的
   所谓的“子”表，带有 DEFAULT false 并确保
   它是NOT NULL。
2. 为每个添加一个 CHECK 约束
   所谓的“子”表来测试
   isremoved = false （或者其他什么
   'boolean' 在你的 SQL 中意味着
   产品）。
3. 添加复合键（例如使用
   UNIQUE 或 PRIMARY KEY）到您的
   所谓的“主”表
   (IsRemoved, <现有关键列
此处>)，或更改现有密钥
   因此。
4. 为每个添加 FOREIGN KEY 约束
   所谓的“子”表可供参考
   上面创建的复合键。

Answer 2

我认为对这个问题本身的评论是非常中肯的。目前，这个问题还很模糊。

但是，假设子表也有一个 IsRemoved 字段 - 毕竟，如果主记录被标记为已删除，子记录仍然可用又有什么意义呢？ - 为什么不在 Master 上实现一个触发器，如果​​ IsRemoved 更改，也会更改 Child 上的 IsRemoved 标志？

这样，就完全消除了检查子节点上主节点状态的需要，因为它们将在涉及活动或非活动状态时保持同步。

Answer 3

您能否在事务中插入除 IsRemove='true' 之外所有值都相同的替换记录，然后删除原始记录？这将为替换记录生成一个新的主键，以便旧的引用无法保留。

我假设您希望检测子项引用已删除记录的情况，并将其视为错误。

Answer 4

如果我们使用以下查询在所有子表中放置了适当的外键约束，则可以找到主数据表的引用表：

SELECT uc.table_name MAIN_TABLE_NAME,
       ucc.column_name MAIN_TABLE_COLUMN_NAME,
       ucc_ref.TABLE_NAME AS REFERENCED_TABLE_NAME,
       ucc_ref.COLUMN_NAME AS REFERENCED_COLUMN_NAME,
       ucc_ref.position
FROM USER_CONSTRAINTS  uc,
     USER_CONS_COLUMNS ucc_ref,
     USER_CONS_COLUMNS ucc
WHERE uc.CONSTRAINT_TYPE = 'R'
  AND uc.R_CONSTRAINT_NAME = ucc_ref.CONSTRAINT_NAME
  AND ucc.Constraint_Name = uc.constraint_name
  AND  d ucc.table_name = uc.table_name
  AND ucc_ref.position = ucc.position
  AND uc.table_name = ? 
ORDER BY ucc_ref.TABLE_NAME,ucc_ref.column_name

此查询在 Oracle 中有效。我不确定其他数据库。

找到引用表后，您需要查找引用表中是否存在要删除的主数据记录。如果子表中存在活动记录，则可以抛出异常。这里的关键是仅找到引用的记录是不够的。我们可能会遇到这样的情况：在子表中找到主记录引用，但子记录也已被取消。我们使用上述概念编写了一个用于主数据删除预检查的实用程序。

Answer 5

使用这个逻辑：

SELECT t1.*
FROM Table1 AS t1
WHERE NOT EXISTS
      ( SELECT *
        FROM Table2 AS t2
        WHERE t2.FKcolumn = t1.PKcolumn
          AND t2.columnX IS NULL
      )